Dynamic pareto table

[Anonymous]

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

[lbendlin · Accepted solution]

Add a small random number to your values to break the ties.

[PaulDBrown · Accepted solution]

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.

[Anonymous]

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

[PaulDBrown]

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

[Anonymous]

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

[lbendlin · Accepted solution]

Add a small random number to your values to break the ties.

[PaulDBrown]

Well, if they have the same value, they have the same rank!

[lbendlin]

@Anonymous I don't understand your Cumulative Total % column examples - shouldn't they end up at 100% for each last row?

[Anonymous]

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? 😃