cancel
Showing results for
Did you mean:
Highlighted
Helper I

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier

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.

``````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!

3 REPLIES 3
Highlighted
Solution Supplier

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.

``````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!

Highlighted
Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Microsoft

Hi @valentina14 ,

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors