Top 5 with comparison to previous years

[VistaDee]

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. 

Thanks in Advance

Dee

[Anonymous · Accepted solution]

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:

Please download and check this Power BI file for a working example that I have created: https://drive.google.com/file/d/1L63-gFAkj1Hg-92WCd3Ren_hC3eWWtXC/view?usp=sharing

[Anonymous · Accepted solution]

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:

Please download and check this Power BI file for a working example that I have created: https://drive.google.com/file/d/1L63-gFAkj1Hg-92WCd3Ren_hC3eWWtXC/view?usp=sharing

[VistaDee]

Hi There @Anonymous 

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

[v-deddai1-msft · Accepted solution]

Hi @VistaDee ，

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

[Anonymous]

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], -1, YEAR )
)

Check this out for more info on PARRALELPERIOD: https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax

[amitchandak]

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

[Greg_Deckler]

@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