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.
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
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
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.
Proud to be a Super User!
Paul on Linkedin.
Thank you so much for your help @PaulDBrown, and not at least @lbendlin.
I am sending you a welldeserved highfive from Denmark
Kind regards
Martin
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
Proud to be a Super User!
Paul on Linkedin.
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
Martin
Add a small random number to your values to break the ties.
Well, if they have the same value, they have the same rank!
Proud to be a Super User!
Paul on Linkedin.
@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? 😃
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |