cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Beckster
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 TableData TableData TableDAX Measure
idyear$year rank
A20194894
A20201,7655
A20218,3821
B20199,3701
B20204,4902
C20203,1864
D20203,5883
D20216,1933
E20195,9063
E20204,9231
F20198,9402
F20216,5422

 

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

 

Year# Accts$ Total
20192 18,310
20202  9,413
20212 14,924
total Accts642,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
20192 9,429
20201 1,765
20212 14,924
total Accts2 26,118

 


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


1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

7 REPLIES 7
yingyinr
Community Support
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] )
        ),
        [$]
    )

yingyinr_0-1613371237927.png

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.

Thank you so much for your help! 🙂

Ashish_Mathur
Super User III
Super User III

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/

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

Thank you so much for your help Ashish! 

You are welcome.


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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors