cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Hi Team,

 

I've following dataset:

CustomerValueSales
A1113.61%4200
A1212.97%4000
A1311.99%3700
A1411.67%3600
A159.08%2800
A88.10%2500
A107.46%2300
A76.48%2000
A96.48%2000
A64.86%1500
A53.24%1000
A31.62%500
A21.30%400
A10.65%200
A40.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!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier
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.

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:

Vale.png

I hope it helps.

Bless you

Fernando

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

View solution in original post

3 REPLIES 3
Highlighted
Solution Supplier
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.

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:

Vale.png

I hope it helps.

Bless you

Fernando

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

View solution in original post

Highlighted
Super User III
Super User III

Hi,

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

Hope this helps.

Customers that make up 80% of sales.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Microsoft
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]))) 

 

Capture1.PNG

 

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))

 

Capture2.PNG

 

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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