Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
flyingfox
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
v-jianboli-msft
Community Support
Community Support

Hi @flyingfox ,

 

Please try:

First unpivot the columns:

vjianbolimsft_0-1674717498549.png

Output:

vjianbolimsft_1-1674717517561.png

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 )

vjianbolimsft_3-1674717606533.png

Final output:

vjianbolimsft_2-1674717595307.png

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.

View solution in original post

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

Hi @flyingfox ,

 

Please try:

First unpivot the columns:

vjianbolimsft_0-1674717498549.png

Output:

vjianbolimsft_1-1674717517561.png

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 )

vjianbolimsft_3-1674717606533.png

Final output:

vjianbolimsft_2-1674717595307.png

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.

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

Your solution works well. Thank you very much

johnt75
Super User
Super User

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 )

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.

DateCategory1Category2Category3Value
Jan-22A1B2C110
Jan-22A2B1C420
Jan-22A3B2C330
Jan-22A2B1C15
Jan-22A1B3C250
Jan-22A4B2C325
Jan-22A3B4C340
Jul-22A3B5C215

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors