Percentage of top N ranked sum values against total values

[flyingfox]

Hello, I am trying to figure out how to implement this use with DAX.

Consider the following matrix with four columns

Category1	Category2	Category3	Value
A1	B2	C1	10
A2	B1	C4	20
A3	B2	C3	30
A2	B1	C1	5
A1	B3	C2	50
A4	B2	C3	25
A3	B4	C3	40

For each Category, I would like to calculate the percentage of the sum of the first two top ranked values (based on the sum) against the total value.

According to the use case, the total sum of the values is 180

For Category1: The top two ranked elements, based on the sum of their values, are A1 and A3 and the sum of the related values is 130. The percentage il 130/180 = 72%

For Category2: The top two ranked elements, based on the sum of their values, are B2 and B3 and the sum of the related values is 115. The percentage il 115/180 = 64%

For Category3: The top two ranked elements, based on the sum of their values, are C2 and C3 and the sum of the related values is 145. The percentage il 145/180 = 81%

Thank you so much for your support

[v-jianboli-msft · Accepted solution]

Hi @flyingfox ,

Please try:

First unpivot the columns:

Output:

Then create a matrix and apply the measure:

Measure =
VAR _a =
    ADDCOLUMNS (
        'Table',
        "Rank",
            RANKX (
                'Table',
                SUMX ( FILTER ( 'Table', [Value.1] = EARLIER ( 'Table'[Value.1] ) ), [Value] ),
                ,
                DESC,
                DENSE
            )
    )
VAR _b =
    SUMX ( FILTER ( _a, [Rank] <= 2 ), [Value] )
VAR _c =
    SUMX ( 'Table', [Value] )
RETURN
    DIVIDE ( _b, _c )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[flyingfox]

Hi everybody, although the solution with the unpivot approach works, I cannot apply it because it messes up the original matrix: for example I cannot use the original columns for other calculations or I cannot append to the table further records in the next months. Therefore I would like to ask you some support to explore another solution. Thanks a lot

[flyingfox]

Your solution works well. Thank you very much

[johnt75]

You can create 3 measures, one for each category, like

VAR SummaryTable =
	ADDCOLUMNS(
		VALUES( 'Table'[Category1] ),
		"@value", [Sum of value]
	)
VAR Top2 = TOPN( 2, SummaryTable, [@value] )
VAR TotalForTop2 = SUMX( Top2, [@value] )
VAR OverallTotal =
	CALCULATE( [Sum of value], REMOVEFILTERS( 'Table' ) )
RETURN
	DIVIDE( TotalForTop2, OverallTotal )

[flyingfox]

Thank you for your reply. Unfortunately I miss something in your proposed solution because for a category, by that measure I am not able to calculate the desidered final total percentage. For example for Category1 it should be 72% and I get a value > 100%. Maybe because I miss to add in the issue another relevant column of the matrix, that is the date (according to this format MMM-YY). I need to calculate the percentage for each category every month.

Date	Category1	Category2	Category3	Value
Jan-22	A1	B2	C1	10
Jan-22	A2	B1	C4	20
Jan-22	A3	B2	C3	30
Jan-22	A2	B1	C1	5
Jan-22	A1	B3	C2	50
Jan-22	A4	B2	C3	25
Jan-22	A3	B4	C3	40
Jul-22	A3	B5	C2	15

[flyingfox]

Hi @johnt75 

I have tried to modify your measure unfortunately without a right result.

With my limited DAX knowledge, analyzing your solution, I might think that in the VAR SummaryTable the [Sum of value] is not correctly calculated for the selected category. In fact if in SummaryTable the second column contains the Category1 variables, then the Column [Sum of Values] should contain only the values filtered by Category1. Could you give any suggestion in order to modify the previous measures? Thank you