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

[MadhumithaV_26]

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 Category	Product Sub Category	Total Sales	Contribution %	Cumulative Sum %	Rank
A	A1	167	25.46% (=167/656)	0%	1
A	A2	150	22.87% (=150/656)	48.32% (=25.46%+22.87%)	2
A	A3	139	21.19% (=139/656)	69.51% (=48.32%+21.19%)	3
A	A4	110	16.77% (=110/656)	86.28% (=69.51+16.77%)	4
A	A5	90	13.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 !!

[BIswajit_Das]

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]

Hello @MadhumithaV_26 This might be what you need

[MadhumithaV_26]

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