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 -
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|
NEED HELP TO CALCULATE = Top 2 for each year
eg = 2021 = A & F, 2020 = B & E, 2019 = B & F
|Year||# Accts||$ Total|
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|
above visuals are filtered if below measure = 1
if year rank <= topN parameter, 1,0
Solved! Go to Solution.
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] ) ), [$] )
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.
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.