cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlbLS Frequent Visitor
Frequent Visitor

Group Smaller Slices with condition in Pie Charts

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%.

 

Problem.JPG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mussaenda Senior Member
Senior Member

Re: Group Smaller Slices with condition in Pie Charts

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

2019_07_10_18_50_14_SmallSlices_Read_Only_Power_BI_Desktop.png

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Group Smaller Slices with condition in Pie Charts

@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

 

Super User
Super User

Re: Group Smaller Slices with condition in Pie Charts

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

AlbLS Frequent Visitor
Frequent Visitor

Re: Group Smaller Slices with condition in Pie Charts

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.

mussaenda Senior Member
Senior Member

Re: Group Smaller Slices with condition in Pie Charts

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

2019_07_10_18_50_14_SmallSlices_Read_Only_Power_BI_Desktop.png

View solution in original post

Super User
Super User

Re: Group Smaller Slices with condition in Pie Charts

@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

AlbLS Frequent Visitor
Frequent Visitor

Re: Group Smaller Slices with condition in Pie Charts

Thank you for your answer!

Could you send me the power bi file please?

 

Best regards,

 

Alberto

AlbLS Frequent Visitor
Frequent Visitor

Re: Group Smaller Slices with condition in Pie Charts

@mussaenda 

 

Thank you for your answer!

Could you send me the power bi file please?

 

Best regards,

 

Alberto

Super User
Super User

Re: Group Smaller Slices with condition in Pie Charts

@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

 

Highlighted
mussaenda Senior Member
Senior Member

Re: Group Smaller Slices with condition in Pie Charts

@AlbLS,

 

link is here.

provide more data for testing if you need more. Your pbix helped me a lot to test the data.

 

@natelpeterson,

it is my first time someone told me i had a good solution.

Thank you, i really appreciate!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,004)