Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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):
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
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
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):
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
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
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
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
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
@VistaDee , put a visual level filter 2020. Or create measure of YTD or 2020 and filter top 5 for that at visual level
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |