Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everybody,
I have a problem that I can not fix, I hope your help can save me!
I would like to group the small slices of a pie chart with one condition, when the percentage of total is less than 5%.
I know the option to create a group by categories in the pie chart but this option does not work because the group always contains the same categories.
Here I found an example for SQL Server, but not for POWER BI:
https://docs.microsoft.com/en-us/sql/reporting-services/report-design/collect-small-slices-on-a-pie-... server-2017
I leave you my Power BI file here:
https://drive.google.com/open?id=1wrs1uh9u8SUOUISg1hvFGqTB_IRUbLtA
I thank you in advance!!
Best regards
Alberto
Solved! Go to Solution.
Hi,
Not expert n PBI but maybe you can try these measures:
GT = CALCULATE(SUM(Feuil1[value]), ALL(Feuil1))
% value = CALCULATE( SUM(Feuil1[value]) / [GT], ALLEXCEPT(Feuil1, Feuil1[categorie]))
Category New = IF([% value] > .05, Feuil1[categorie], "Others" )
This has been submitted as a feature request. Click here to upvote:
https://ideas.powerbi.com/ideas/idea/?ideaid=a1717c80-2b97-eb11-89ee-281878e6452b
Hi,
Not expert n PBI but maybe you can try these measures:
GT = CALCULATE(SUM(Feuil1[value]), ALL(Feuil1))
% value = CALCULATE( SUM(Feuil1[value]) / [GT], ALLEXCEPT(Feuil1, Feuil1[categorie]))
Category New = IF([% value] > .05, Feuil1[categorie], "Others" )
Thank you for your answer!
Could you send me the power bi file please?
Best regards,
Alberto
Thank you for your answer!
Could you send me the power bi file please?
Best regards,
Alberto
@AlbLS -
@mussaenda has a good solution - similar to my static table solution, except adding to the original table, which is a better idea. You can accomplish this with a new Calculated Column:
New Category = var total_value = CALCULATE(SUM(Feuil1[value]),ALL(Feuil1)) var cat_value = CALCULATE(SUM(Feuil1[value]),ALLEXCEPT(Feuil1, Feuil1[categorie])) return IF(DIVIDE(cat_value, total_value) < .05, "OTHERS (<5%)", Feuil1[categorie])
Then simply replace the category with the new column in your pie chart.
Note: Again, this is a static solution, which won't interact with other slicers, like the Year you mentioned. The reason is that columns and tables have static values, while Measures are dynamic.
Cheers,
Nathan
Hello @mussaenda and @Anonymous
Thank you again to both of you.
@mussaenda you can be proud because your solution really works perfectly, I have a fairly complex dataset, with a date table and others, and the solution works perfectly even when I filter by year or others.
Thank you to both of you for the time, you really helped me.
Best regards,
Alberto
It's impossible for this to work ”perfectly”, or at all, because you cannot use a measure as a pie chart category. You need a calculated column, but then you have to take care of the correct summation sumhow.
@AlbLS - Try the following steps:
1. Create a new Calculated Table for categories:
Categories = UNION( VALUES(Feuil1[categorie]), DATATABLE("categorie", STRING, {{"OTHER (<5%)"}}) )
2. Create a Relationship between the existing table and the new table.
3. Create the following Measure:
Substitute Value = var total_value = CALCULATE( SUM(Feuil1[value]), ALL(Categories[categorie]) ) var current_value = SUM(Feuil1[value]) var current_category = MAX(Categories[categorie]) return IF( current_category = "OTHER (<5%)", var sum_table = SUMMARIZE( ALL(Categories), Categories[categorie], "Only Small Percentage", IF(DIVIDE(SUM(Feuil1[value]), total_value) < .05, SUM(Feuil1[value]), BLANK()) ) return SUMX(sum_table,[Only Small Percentage]) , IF( DIVIDE( current_value, total_value ) < .05, BLANK(), current_value ) )
4. Replace the values in your pie chart with the new Category table and the new measure.
Hope this helps,
Nathan
Was bouncing around the web looking for a solution to this problem, must have tried a dozen different things before I found this one. Worked like a charm, thanks much for this post.
It worked, thanks.
Thanks Nathan, this worked a treat! Great job
Hi natelpeterson,
Thank you very much for your answer.
I tried the first solution in my original file (it is confidential, I can not share it) and it works well at first, but when I filter by year it does not work anymore, the category "Others (<5 %)" disappears. Only works with the total of years.
The second solution has the same problem.
I thank you very much.
@AlbLS - I think it will work if you separate year into another "Dimension" table. A best practice is to use descriptive attributes from dimension tables and not in your "Fact" tables.
Cheers,
Nathan
Alternatively, here's a simpler, but non-dynamic (it won't interact with other selections) solution:
1. Create a new Calculated Table:
Table2 = var total_value = SUM(Feuil1[value]) var sum_table = SUMMARIZE( Feuil1, Feuil1[categorie], "Sum Value", SUM(Feuil1[value]), "New Category", IF(DIVIDE(SUM(Feuil1[value]), total_value)< .05, "OTHERS (<5%)", MAX(Feuil1[categorie])) ) return sum_table
2. Use "New Category" and "Sum Value" in the pie chart. You can also add categorie as second level in pie chart for drilling down.
Hope this helps,
Nathan
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 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |