Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MadhumithaV_26
Frequent Visitor

How to filter out the tables based on the Top 50% Contribution of Total Sales?

Hi All,

 

Consider the below table where we have Product Category & Product Subcategory and the corresponding Total Sales.

 

We need get only the Product Category & sub category which contributes the Top 50% of the Total Sales.

Step 1 - Calculate the Contribution % for each row. (i.e) = 167 / 656(Total Sales)
Step 2 - Calculate Cumulative sum of Contribution %

Step 3 - From Step 2, get only the records which is in the range of 0% <= x <= 50%

In the below example, only first two records will come. So the table visual should filter accordingly and show only first 2 records.

 

Product CategoryProduct Sub CategoryTotal SalesContribution %Cumulative Sum %Rank
AA116725.46% (=167/656)0%1
AA215022.87% (=150/656)48.32% (=25.46%+22.87%)2
AA313921.19% (=139/656)69.51% (=48.32%+21.19%)3
AA411016.77% (=110/656)86.28% (=69.51+16.77%)4
AA59013.72% (=90/656)100.00% (=86.28%+13.72%)5
  Total = 656 (=167+150+139+110+90)   

 

Please help me in achieving the above logic. Thanks in advance !!

3 REPLIES 3
BIswajit_Das
Resolver III
Resolver III

Hello @MadhumithaV_26 You can use SUMX to avoid any error Try out the below DAX
Calculated Columns :-

i.e

Contribution % =
VAR _x = SUMX('XOX',[Total Sales])
VAR _y = [Total Sales]
RETURN _y / _x
And
Cumulative Sum % =
VAR _res =
CALCULATE(
    SUM('XOX'[Contribution %]),
    FILTER(
        ALL('XOX'),
        'XOX'[Contribution %] >= EARLIER('XOX'[Contribution %])
    )
)
RETURN
IF(_res = 'XOX'[Contribution %],0,_res)
BIswajit_Das
Resolver III
Resolver III

Hello @MadhumithaV_26 This might be what you need
C1.PNGC2.PNG

Hi @BIswajit_Das 

 

This is not working for me. Total Sales is a measure in my use case. So I can't able to use sum or Earlier function on top of a measure. Can you help me solving this?

Thanks

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.