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,
I am trying to create a column chart to show the top 5 customers depening on the quarter. However I noticed something strange. The top 5 filtering works just fine, but only applies to the last quarter.
In the pictures you see that the Top5 for qtr 1 2018 should be customer 1, 2, 3, 4 and 5. However as they are not the Top 5 in the last visible quarter, Power BI ommits them and shows the last Top 5 customers also in the first visible quarter.
Is there a way, or measure, to always show the top 5? Even if that means the legend will display more than 5 customers?
Thank you very much!
Solved! Go to Solution.
Hi @Anonymous ,
To create a calculated column as below and make it to be greater than or equal to 5.
rank = RANKX ( FILTER ( 'Table', 'Table'[date] = EARLIER ( 'Table'[date] ) ), CALCULATE ( SUM ( 'Table'[values] ), ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[customer No] ) ), , ASC, DENSE )
Hi @Anonymous ,
To create a calculated column as below and make it to be greater than or equal to 5.
rank = RANKX ( FILTER ( 'Table', 'Table'[date] = EARLIER ( 'Table'[date] ) ), CALCULATE ( SUM ( 'Table'[values] ), ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[customer No] ) ), , ASC, DENSE )
Hey @v-frfei-msft ,
Thank you very much for your reply. It works indeed. I was also wondering, whether it is possible to add an additional argument/ attribute. For example a city or country?
Hi @Anonymous ,
To add the city or country in ALLEXCEPT.
rank = RANKX ( FILTER ( 'Table', 'Table'[date] = EARLIER ( 'Table'[date] ) ), CALCULATE ( SUM ( 'Table'[values] ), ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[customer No],[CITY],[COUNTRY] ) ), , ASC, DENSE )
If it doen't meet your requirement, kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |