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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors