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

Frequent Visitor

## Help with Complex Rank

Hi Guys
Am hoping can help guide me in the right direction with some ranking. I've spent far too long trying to get this to work so need some help.

I have a table, along with a dax measure to calculate value rank. I am wanting to use this to filter matrix tables with a parameter to filter by top N Rank. (I am using this to filter with another measure that is (if rank <-N then ,1,0) - I just filter the measure to 1.

I have a matrix with # ids and value with rows of year. If I filter to top N (say, 200), it is taking the top 200 ids ranked over all of these years.

I am after 2 different versions -

1.  top N at row(year) level in matrix
2. values based on top N of latest year in matrix

Below is an idea of what I am after. The calculations need to be dynamic to allow for filters by region, id type etc.
Any help is appreciated!

 Data Table Data Table Data Table DAX Measure id year \$ year rank A 2019 489 4 A 2020 1,765 5 A 2021 8,382 1 B 2019 9,370 1 B 2020 4,490 2 C 2020 3,186 4 D 2020 3,588 3 D 2021 6,193 3 E 2019 5,906 3 E 2020 4,923 1 F 2019 8,940 2 F 2021 6,542 2

NEED HELP TO CALCULATE = Top 2 for each year
eg = 2021 = A & F, 2020 = B & E, 2019 = B & F

 Year # Accts \$ Total 2019 2 18,310 2020 2 9,413 2021 2 14,924 total Accts 6 42,647

NEED HELP TO CALCULATE = Top 2 for latest year, and their totals for prev years (if exist)
ie - top 2 for 2021 is A & F, so the 2020 & 2019 are results for A&F (in 2020 only A had a result)

 Year # Accts \$ Total 2019 2 9,429 2020 1 1,765 2021 2 14,924 total Accts 2 26,118

above  visuals are filtered if below measure = 1
if year rank <= topN parameter, 1,0

1 ACCEPTED SOLUTION
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
7 REPLIES 7
Community Support

Hi @Beckster ,

You can follow the below steps to get it, please find the details in the attachment.

1. Create a date table

2. Create a measure as below to get the rank

``````Rank = RANKX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[year] = MAX ( 'Table'[year] ) ),
CALCULATE ( SUM ( 'Table'[\$] ) ),
,
DESC,
DENSE
)``````

3. Create two measures to get the sum of \$ for top 2 and ids for top 2 in latest year separately

``Total for Top 2 = SUMX ( FILTER('Table',[Rank]<=2&&'Table'[year]=SELECTEDVALUE('Date'[Year])), [\$] )``
``````Total for ids =
VAR _maxyear =
CALCULATE ( MAX ( 'Table'[year] ), ALLSELECTED ( 'Table'[year] ) )
VAR _tab =
CALCULATETABLE (
VALUES ( 'Table'[id] ),
FILTER ( 'Table', 'Table'[year] = _maxyear && [Rank] <= 2 )
)
RETURN
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[id]
IN _tab
&& 'Table'[year] = SELECTEDVALUE ( 'Date'[Year] )
),
[\$]
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thank you so much for your help! 🙂

Super User

Hi,

In the first table, why should the answer for 2021 be 6,542.  Should it not be 6,542 + 8,382 = 14,924.  This will be the total of the Top 2.  The same logic should run for other other years as well in first table.  Please clarify.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Thanks Ashsih for your pickup - yes you were right, I had only typed in 2nd rank in error in my post.

Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

Thank you so much for your help Ashish!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

## Helpful resources

Announcements

#### 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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors