cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VistaDee
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:

 

ClientNameClientRankYear
Client A12020
Client B22020
Client C32020
Client D42020
Client E52020
Client F62020
Client A12019
Client B32019
Client C42019
Client D72019
Client E22019
Client F52019
Client A12018
Client B82018
Client C102018
Client D42018
Client E52018
Client F72018

 

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

ClientName201820192020
Client A111
Client B832
Client C1043
Client D474
Client E525

 

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

 

Thanks in Advance

Dee

2 ACCEPTED SOLUTIONS
adriansuteu933
Helper II
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.

 

misc1.png

 

 

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:

 

misc2.png

 

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

 

View solution in original post

Hi @VistaDee ,

 

You can refer to the measure below:

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

 

Capture.PNG

 

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

View solution in original post

6 REPLIES 6
adriansuteu933
Helper II
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.

 

misc1.png

 

 

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:

 

misc2.png

 

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

 

View solution in original post

Hi There @adriansuteu933 

 

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

Hi @VistaDee ,

 

You can refer to the measure below:

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

 

Capture.PNG

 

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

View solution in original post

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 )
)

 

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

 

 

amitchandak
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User IV
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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors