Calculating the number of rows until it reaches 80, Sorted descending

[Anonymous]

Hi Team,

I've following dataset:

Customer	Value	Sales
A11	13.61%	4200
A12	12.97%	4000
A13	11.99%	3700
A14	11.67%	3600
A15	9.08%	2800
A8	8.10%	2500
A10	7.46%	2300
A7	6.48%	2000
A9	6.48%	2000
A6	4.86%	1500
A5	3.24%	1000
A3	1.62%	500
A2	1.30%	400
A1	0.65%	200
A4	0.49%	150

I want to create a measure that gives me count of rows till it reaches 80. 

I tried following dax:

Count of Products Making 80% of Margin =

COUNTROWS(FILTER(Sales,Sales[Value]<=80))
But it gives me answer 15, since it calculates all those rows which sums <=80.

I want to sort the percentage column(value) in descending order and count the rows until my sum is <=80.

In above case it should give me Count = 5.

I need a measure since I want to use it in a card.

Thanks in advance!

[v-deddai1-msft]

Hi @Anonymous ,

I suggest create two calculated column one for rank and the other one for the cumulative value:

Rank = RANKX('Table','Table'[Value],,DESC) 
Cvalue = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Rank]<=EARLIER('Table'[Rank]))) 

Then you can create a measure for card visual  to gives me count of rows till  cumulative value reaches 80%.

Measure = CALCULATE(MAX('Table'[Rank]),FILTER('Table','Table'[Cvalue]<=0.8))

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EambX0CGNMlFq-kc-7...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[LukeMcManagan]

I've spent most of my day trawling forums, YouTube and blogs for this answer. THANK YOU. I know calculated columns aren't advisable but this gets the job done way quicker than attempting a similar output in a measure. My visuals wouldn't even load due to the volume of data I'm working with but at least my visuals render once this is calculated per row and stamped on the table. Thanks again!

[Ashish_Mathur]

Hi,

The answer should be 7.  You may download my PBI file from here.

Hope this helps.

[calerof · Accepted solution]

Hello @valentina14 ,

You need %cummulatvie sales, not the only one. You can follow the large @AlbertoFerrari to calculate the ABC Classification in DAX Patterns for products and adapt it to your needs.

You must add 4 columns:

1) Customer Sales = Valentina[Sales]

2) Cummulative Sales = 
VAR CurrentCustomerSales = Valentina[Customer Sales]
VAR BetterCustomers =
    FILTER (
        Valentina,
        Valentina[Customer Sales] >= CurrentCustomerSales
    )
VAR Result =
    SUMX (
        BetterCustomers,
        Valentina[Customer Sales]
    )
RETURN
    Result

3) Cummulative Pct = 
DIVIDE (
    Valentina[Cumulated Sales],
    SUM ( Valentina[Customer Sales] )
)

4) Pareto Class = 
SWITCH (
    TRUE,
    Valentina[Cummulative Pct] <= 0.8, "80% Pareto",
    Valentina[Cummulative Pct] <= 1, "20% Rest"
)

And you understand this:

I hope it helps.

Bless you

Fernando

P.S. If it helped, please consider liking the post and mark it as an answer!