Power BI - Calculate Percentage Measure based on Top N Selection

Question

I have a table like this.

Company Amount Year
A   200 2016
B   300 2016
C   400 2016
A   500 2017
B   600 2017
C   700 2017
A   100 2016
B   400 2016
C   100 2016
A   600 2017
B   133 2017
C   50  2017

I am looking for a measure calculate the Percentage of amount that top 2 companies(based on amount) contributes to that particular year's total amount. This needs to be dynamic based on the values of Year slicer. (For Example if 2 years are selected, then the top 2 companies needs to be based on the total amount that the company has spent on those 2 years).

Alexis Olson Alexis Olson · Accepted Answer · 2018-04-19T19:26:53

How about this as a measure?

PercentTop2 =
    DIVIDE(
        SUMX(
            TOPN(2,
                SUMMARIZECOLUMNS(Companies[Company],
                    "Amount", SUM(Companies[Amount])),
                [Amount]),
            [Amount]),
        SUMX(ALLSELECTED(Companies), Companies[Amount]))

The TOPN(2,[...]) finds the top 2 rows of the summarized table. Then you divide the sum of those two rows by the sum of all the selected rows.

Power BI - Calculate Percentage Measure based on Top N Selection

1 Answers