Calculate Top 10% using percentiles by Year

[meghansh]

Hi Folks,

I am stuck at this problem. I have the following data:
I need to calculate Top 10% Users (ID) based on the amount for each year (using year as a slicer). Thank you.

ID	Year	Amount
5487	2020	205987
5487	2021	152659
5487	2022	548751
5459	2019	326985
5459	2020	321549
5459	2021	547854
5459	2022	652301
4589	2019	-987326
4589	2020	12545
4589	2021	54700
4589	2022	264523
1021	2020	8423
1021	2021	45622
1021	2022	187932
9856	2019	32215
9856	2020	910321
9856	2021	212566
9856	2022	10221
5658	2019	102355
5658	2020	6456
5658	2021	15654
5658	2022	48998
8799	2020	178469
8799	2021	54545
1023	2019	81212
4665	2019	851025
4665	2020	-25478
4665	2021	5215
4665	2022	11006

[Ashish_Mathur]

Hi,

These measures work

ID count = COUNTROWS(Data)

10% of ID count = ROUNDUP(0.1*[ID count],0)

Total = SUM(Data[Amount])

Measure = CALCULATE([Total],TOPN([10% of ID count],ALL(Data[ID]),[Total]),VALUES(Data[ID]))

Hope this helps.

[v-yaningy-msft · Accepted solution]

Hi, @meghansh 

You can try the following DAX.

DAX:

Rank = 
RANKX (
    FILTER ( 'Table', 'Table'[Year] = EARLIER ( 'Table'[Year] ) ),
    'Table'[Amount],
    ,
    DESC,
    SKIP
)

Filter ID = 
VAR _rows =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] ),
        ALL ( 'Table' )
    )
VAR _10Percent = _rows * 0.1
VAR _result =
    IF ( SELECTEDVALUE ( 'Table'[Rank] ) <= _10Percent, 1, 0 )
VAR _result1 =
    IF ( _rows < 20 && SELECTEDVALUE ( 'Table'[Rank] ) = 1, 1, 0 )
RETURN
    IF ( ISFILTERED ( 'Table'[Year] ), IF ( _rows < 20, _result1, _result ), 1 )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum