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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
meghansh
Frequent Visitor

Calculate Top 10% using percentiles by Year

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.

IDYearAmount
54872020205987
54872021152659
54872022548751
54592019326985
54592020321549
54592021547854
54592022652301
45892019-987326
4589202012545
4589202154700
45892022264523
102120208423
1021202145622
10212022187932
9856201932215
98562020910321
98562021212566
9856202210221
56582019102355
565820206456
5658202115654
5658202248998
87992020178469
8799202154545
1023201981212
46652019851025
46652020-25478
466520215215
4665202211006
1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

Hi, @meghansh 

You can try the following DAX.

vyaningymsft_0-1714011932613.png

vyaningymsft_1-1714011981769.pngvyaningymsft_2-1714011991050.png

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

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1714015450122.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yaningy-msft
Community Support
Community Support

Hi, @meghansh 

You can try the following DAX.

vyaningymsft_0-1714011932613.png

vyaningymsft_1-1714011981769.pngvyaningymsft_2-1714011991050.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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