cancel
Showing results for
Did you mean:
Frequent Visitor

## Top 5 with comparison to previous years

Hi There

I hope you can help 🙂

I have data showing the rankings of clients over the years. What I want to do is show just the top 5 for 2020 but see where they placed in previous years in matrix form.

So here is the raw data:

 ClientName ClientRank Year Client A 1 2020 Client B 2 2020 Client C 3 2020 Client D 4 2020 Client E 5 2020 Client F 6 2020 Client A 1 2019 Client B 3 2019 Client C 4 2019 Client D 7 2019 Client E 2 2019 Client F 5 2019 Client A 1 2018 Client B 8 2018 Client C 10 2018 Client D 4 2018 Client E 5 2018 Client F 7 2018

And here is how I want it to display in the matrix

 ClientName 2018 2019 2020 Client A 1 1 1 Client B 8 3 2 Client C 10 4 3 Client D 4 7 4 Client E 5 2 5

So I can see the top 5 for 2020 but also see where they placed in the past few years.

Dee

2 ACCEPTED SOLUTIONS
Helper II

Hello.

Step 1: In case you have all the data in a single table, first of all you have to create a separate table with unique values for years and then create a relation between the new table with Years and the initial one on the Year Column.

Step 2: Create one measure for each year (2020, 2019, 2018):

Spoiler
2020 rank =
CALCULATE (
SUM ( ClientRanks[Rank] ),
ALLEXCEPT ( ClientRanks, ClientRanks[Client] ),
Years[Year] = 2020
)

Step 3: Create a table (not a matrix) and add the 3 measures as columns, as well as the Client name.

Step 4: Click on the newly created table and add a Visual level filter to display only the Top N values (I picked top 2) by using the technique below:

Community Support

You can refer to the measure below：

``Measure = IF(CALCULATE([rank],'Table'[Year] = 2020)>5,BLANK(),[rank])``

Since my test data is different from your actual data, the output on the screenshot is not the same as yours. And you can refer to the pbix file：https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYAA8XhqEgBDv6FKH6...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

6 REPLIES 6
Helper II

Hello.

Step 1: In case you have all the data in a single table, first of all you have to create a separate table with unique values for years and then create a relation between the new table with Years and the initial one on the Year Column.

Step 2: Create one measure for each year (2020, 2019, 2018):

Spoiler
2020 rank =
CALCULATE (
SUM ( ClientRanks[Rank] ),
ALLEXCEPT ( ClientRanks, ClientRanks[Client] ),
Years[Year] = 2020
)

Step 3: Create a table (not a matrix) and add the 3 measures as columns, as well as the Client name.

Step 4: Click on the newly created table and add a Visual level filter to display only the Top N values (I picked top 2) by using the technique below:

Frequent Visitor

This is great and has almost worked.... the one issue Im having is, the rank field is a measure calculated using RANKX on the quantities brought in by the client. When I add a filter to the 2020Rank "Less that or equal to 5" it seems to change the values in the Rank Columns.... like its nearly recalculating based on the results in the filter

Dee

Community Support

You can refer to the measure below：

``Measure = IF(CALCULATE([rank],'Table'[Year] = 2020)>5,BLANK(),[rank])``

Since my test data is different from your actual data, the output on the screenshot is not the same as yours. And you can refer to the pbix file：https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYAA8XhqEgBDv6FKH6...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Helper II

You might want to remove the ALLEXCEPT part of the DAX expression and you will have the same result in usual cases. This depends on the behaviour that you expect with other filters, in case there are any other filters/slicers that you will use. More info about ALLEXCEPT: https://docs.microsoft.com/en-us/dax/allexcept-function-dax

2018 rank =
CALCULATE ( SUM ( ClientRanks[Rank] ), Years[Year] = 2018 )

In case you have a Time intelligence table that is related to your table, the second part of the DAX for each measure changes (In the example below DateTable is the table with Time Intelligence and DateKey is the column with the calendaristic date).

2018 rank =
CALCULATE (
SUM ( ClientRanks[Rank] ),
PARALLELPERIOD ( DateTable[DateKey], -1YEAR )
)

Super User IV

@VistaDee , put a visual level filter 2020. Or create measure of YTD or 2020 and filter top 5 for that at visual level

Proud to be a Super User!

Super User IV

@VistaDee Will try to look deeper into this, but seems like you want a Complex Selector. This can get tricky with a matrix. See page 3 I think of the Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements