Forum Discussion
Dynamic pareto table
I hope that you are in for a treat... a brain squeezing treat!
I want to make a pareto curve that can adjust itself based on selected Country.
I have the following data:
| Country | Company Members | Units |
| V-CH | 14425320 | 104 |
| V-DK | 13743530 | 94 |
| V-DK | 12649180 | 85 |
| V-D | 10038211 | 74 |
| V-F | 10228377 | 69 |
| V-I | 948451 | 64 |
| V-DK | 14189021 | 63 |
| V-CH | 10440101 | 61 |
| V-DK | 12681259 | 61 |
| V-D | 10068976 | 54 |
| V-CH | 13303890 | 50 |
| V-CH | 10429408 | 45 |
| V-DK | 12644728 | 43 |
| V-D | 10044967 | 42 |
| V-I | 948465 | 42 |
| V-D | 10039021 | 39 |
| V-CH | 10433322 | 38 |
| V-DK | 13273111 | 36 |
| V-DK | 12649179 | 36 |
| V-A | 10446560 | 36 |
| V-I | 948521 | 36 |
I would like to visualize a table that shows: 1) Rank, 2) Cumulative Total, and 3) Cumulative Total %.
| Country | Company Members | Units | Rank | Cumulative | Cumulative % |
| V-CH | 14425320 | 104 | 1 | 104 | 1,0% |
| V-DK | 13743530 | 94 | 2 | 198 | 2,0% |
| V-DK | 12649180 | 85 | 3 | 283 | 2,8% |
| V-D | 10038211 | 74 | 4 | 357 | 3,6% |
| V-F | 10228377 | 69 | 5 | 426 | 4,2% |
| V-I | 948451 | 64 | 6 | 490 | 4,9% |
| V-DK | 14189021 | 63 | 7 | 553 | 5,5% |
| V-CH | 10440101 | 61 | 8 | 614 | 6,1% |
| V-DK | 12681259 | 61 | 9 | 675 | 6,7% |
| V-D | 10068976 | 54 | 10 | 729 | 7,3% |
| V-CH | 13303890 | 50 | 11 | 779 | 7,8% |
| V-CH | 10429408 | 45 | 12 | 824 | 8,2% |
| V-DK | 12644728 | 43 | 13 | 867 | 8,6% |
| V-D | 10044967 | 42 | 14 | 909 | 9,1% |
| V-I | 948465 | 42 | 15 | 951 | 9,5% |
| V-D | 10039021 | 39 | 16 | 990 | 9,9% |
| V-CH | 10433322 | 38 | 17 | 1028 | 10,2% |
| V-DK | 13273111 | 36 | 18 | 1064 | 10,6% |
| V-DK | 12649179 | 36 | 19 | 1100 | 11,0% |
| V-A | 10446560 | 36 | 20 | 1136 | 11,3% |
| V-I | 948521 | 36 | 21 | 1172 | 11,7% |
In addition, users wish to have the ability to filter on country e.g. If they filter on "V-DK" (Denmark), they wish to have the following result:
| Country | Company Members | Units | Rank | Total | Cumulative | Cumulative % |
| V-DK | 13743530 | 94 | 1 | 1108 | 94 | 8,5% |
| V-DK | 12649180 | 85 | 2 | 1108 | 179 | 16,2% |
| V-DK | 14189021 | 63 | 3 | 1108 | 242 | 21,8% |
| V-DK | 12681259 | 61 | 4 | 1108 | 303 | 27,3% |
| V-DK | 12644728 | 43 | 5 | 1108 | 346 | 31,2% |
| V-DK | 13273111 | 36 | 6 | 1108 | 382 | 34,5% |
| V-DK | 12649179 | 36 | 7 | 1108 | 418 | 37,7% |
Can anyone help me on how to acheive this in PowerBI? 🙂
Kind Regards
Martin
Add a small random number to your values to break the ties.
Great solution lbendlin ! Hadn't thought of that!
Ok so following this great idea, create the following:
Add a new column to the fact table with the following:
and then:
Unit temp = [Sum Units] + SUM(FactTable[Random])RNK Temp = RANKX(ALLSELECTED(FactTable), [Unit temp],,DESC,Dense)Cumulative = VAR RNK = [RNK Temp] RETURN IF ( ISBLANK ( [Sum Units] ), BLANK (), IF ( ISINSCOPE ( 'Dim Country'[DCountry] ), CALCULATE ( [Unit temp], FILTER ( ALLSELECTED ( FactTable ), [RNK Temp] <= RNK ) ) ) )% Cumul = VAR _Total = CALCULATE([Unit temp], ALLSELECTED(FactTable)) RETURN DIVIDE([Cumulative], _Total)You can decide what to display in the visual. I've kept the original [Sum Units] and [Rank] measures.
8 Replies
Great solution lbendlin ! Hadn't thought of that!
Ok so following this great idea, create the following:
Add a new column to the fact table with the following:
and then:
Unit temp = [Sum Units] + SUM(FactTable[Random])RNK Temp = RANKX(ALLSELECTED(FactTable), [Unit temp],,DESC,Dense)Cumulative = VAR RNK = [RNK Temp] RETURN IF ( ISBLANK ( [Sum Units] ), BLANK (), IF ( ISINSCOPE ( 'Dim Country'[DCountry] ), CALCULATE ( [Unit temp], FILTER ( ALLSELECTED ( FactTable ), [RNK Temp] <= RNK ) ) ) )% Cumul = VAR _Total = CALCULATE([Unit temp], ALLSELECTED(FactTable)) RETURN DIVIDE([Cumulative], _Total)You can decide what to display in the visual. I've kept the original [Sum Units] and [Rank] measures.
Thank you so much for your help PaulDBrown, and not at least lbendlin.
I am sending you a welldeserved highfive from DenmarkKind regards
Martin
Anonymous I don't understand your Cumulative Total % column examples - shouldn't they end up at 100% for each last row?
Hi lbendlin ,
you are totalt right and sorry for the confusion. I thought I could add the Excel calculation as documentation, but was instead forced to copy a part of it.
nevertheles, do you know how it can be solved? 😃
With this model:
and the following measures:
Sum Units = SUM(FactTable[Units])Rank = IF ( ISBLANK ( [Sum Units] ), BLANK (), IF ( ISINSCOPE ( 'Dim Country'[DCountry] ), RANKX ( ALLSELECTED ( FactTable ), [Sum Units],, DESC, DENSE ) ) )Cumulative = VAR RNK = [Rank] RETURN CALCULATE ( [Sum Units], FILTER ( ALLSELECTED ( FactTable ), [Rank] <= RNK ) )% Cumul = VAR _Total = CALCULATE ( [Sum Units], ALLSELECTED ( FactTable ) ) RETURN DIVIDE ( [Cumulative], _Total )You will get:
I've attached the sample PBIX file
Hi PaulDBrown
Thank you so much for your feedback! Your solution is very close to what I need 😄
I notice that your "Rank", "Cumulative total" and "Cumulative %" does not count values that aren't unique. E.g. when filtering on Denmark, there are 2 "company members" that have sold 36 units, and your rank subsequently provides them with the same value, which also affects your "cumulative total" and "Cumulative %"
Do you have an easy fix for this issue? 😄
Kind regards
MartinAdd a small random number to your values to break the ties.