Create percentage card for top 10 sum of column

[Humanoidhuman]

Hi,

Background: I am new to using power BI. I have imported a dataset from excel which contains a value for which I have managed to get a sum of. One of my cards displays the sum and another one I have used basic filtering to display the top 10 within the sum of the values in that column.

Problem: I want to create a card which would display the the amount of the top 10 values from the total in percentage. An added layer is that the sum of the column for the top 10 is calculated based on another column [see example below]. The solutions that I found generally relate to scenarios where a new measure is created based on existing columns, but none based on filtered values within the column (top 10).

Example Table: I have something similar to a table metnioned below for a little more clarity regarding my concern. Basically my issue is to do caluclations on column C.

Name [A]	Worked Hours [B]	Overtime Hours [C]
Jack	12	4
Jill	10	2
Joann	13	5
Jack	9	1
Joe	15	7
Joann	20	12
Jay	8	0
Joann	9	1

Anticipated Solution: I want a card in my dashboard which will display the top 10 from my calculated sum of values in percentage form. 

TIA!

[v-zhengdxu-msft]

Hi @Humanoidhuman 

Could you please explain the logic of the percentage?

Although I read your post over and over again, I can't understand what is your expected overcome. 

Best Regards

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

[Humanoidhuman]

I apologize if I was not clear. So for example, the total overtime hours in the above example table would be 31, and the total overtime hours for would be 5, which would be 16% of the total overtime hours. 
As of now, I am able to get to the number 5, but not able to convert it into percentage.

To put it in perspective, my card in my BI is filtered out to diplay the total overtime hours of the top 3 people with the most overtime hours. I have been able to use the TopN filter to display that (which shows the total number of OT hours) but I want it to be displayed as a percentage. Like for the above example, it would be 25 hours, but I would like it to be displayed as 80.64%.

I hope this makes more sense. 

[v-zhengdxu-msft · Accepted solution]

Hi @Humanoidhuman 

Please try this:

I create a calculate column:

percentage =
VAR _SumOTH =
    SUMX ( ALL ( 'Table' ), 'Table'[Overtime Hours] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Overtime Hours] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
    ) / _SumOTH

This return the percentage of the overtime of every person:

Then add a sort column:

Sort =
RANKX (
    'Table',
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Name] = EARLIER ( 'Table'[Name] ) ),
        'Table'[Overtime Hours]
    ),
    ,
    DESC,
    DENSE
)

Finally, the top 3 column can be like:

Top3 =
SUMX ( FILTER ( 'Table', 'Table'[Sort] <= 3 ), 'Table'[Overtime Hours] )
    / SUM ( 'Table'[Overtime Hours] )

The result is as follow:

Best Regards

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

[Humanoidhuman]

@v-zhengdxu-msft This worked for me thank you very much. Although I would like to extend the result into displaying the 'total' percentage of the top 3. For now I have used th ebasic sum function for that. Also I marked your answer as the solution in case anyone else is trying to do something similar.