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.
I created a Rank column and a custom measure. I've filtered on Rank to show less than 11, so basically show me the top 10.
The custom measure is basically saying to show me when rank <= 5, then show Amount else Others.
Instead of "Others", is there a way to sum up the Amount value and end up only showing 6 rows. First 5 rows shows the top 5 countries, and then the 6th row sums up the countries that are ranked from 6 to 10.
Solved! Go to Solution.
Here is the solution:
1. Goto modelling tab and click new table and add following dax :
Country with other = UNION(Values('Dim - Country'[Country Name]), ROW("Country Name", "Others"))
it will create a new table with all the countries and addition country "Others"
2. Set relationship with this new country with your original table (it will be 1 to many)
3. change your country rank measure like this
Ctry Rank = RANKX(ALL('Country with others'[Country Name]), 'Dim - Country'[Total])
4. Add new measure "Total with other" -> This is final measure we need
Total with other = if([Ctry Rank] <= 10, 'Dim - Country'[Total], if(HASONEVALUE('Country with others'[Country Name]), if(values('Country with others'[Country Name]) = "Others", sumx(filter(all('Country with others'[Country Name]), [Ctry Rank ] > 10), 'Dim - Country'[Total]) ) ) )
5. Add new table visual , drop country name from "Country with other table" and Total with other measure
I expect that you will top 10 countries with their value and rest should be all summed up with "Others"
PS - There could be typo in DAX expression, let me know if it doesn't work
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |