Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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!
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-72FNzIBEBkUDhTNJJgiT__a5m8IAw?e=hln0yM
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
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!
Hi,
The answer should be 7. You may download my PBI file from here.
Hope this helps.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |