CODEWITHSUNDEEP
powerbidax
Student of the Digital World
Student of the Digital World

Reputation: 3041

Power BI - Calculate Percentage Measure based on Top N Selection

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).

Upvotes: 2

Views: 2007

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Upvotes: 2

Related Questions