Help with Complex Rank

[Beckster]

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

[v-yiruan-msft]

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

[Beckster]

Thank you so much for your help! 🙂

[Ashish_Mathur]

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.

[Beckster]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Beckster]

Thank you so much for your help Ashish! 

[Ashish_Mathur]

You are welcome.