cancel
Showing results for
Did you mean: Regular Visitor

## Percentage of top N ranked sum values against total values

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

1 ACCEPTED SOLUTION  Community Support

Hi @flyingfox ,

First unpivot the columns: Output: Then create a matrix and apply the measure:

``````Measure =
VAR _a =
'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.

6 REPLIES 6  Community Support

Hi @flyingfox ,

First unpivot the columns: Output: Then create a matrix and apply the measure:

``````Measure =
VAR _a =
'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. Regular Visitor

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 Regular Visitor

Your solution works well. Thank you very much  Super User

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

``````VAR SummaryTable =
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 )`````` Regular Visitor

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 Regular Visitor

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  