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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.