cancel
Showing results 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

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.

 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
1 ACCEPTED SOLUTION
Community Support

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!

2 REPLIES 2
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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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!