Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlbLS
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
mussaenda
Super User
Super User

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

15 REPLIES 15
LuisV805
Frequent Visitor

This has been submitted as a feature request. Click here to upvote:

 

https://ideas.powerbi.com/ideas/idea/?ideaid=a1717c80-2b97-eb11-89ee-281878e6452b

mussaenda
Super User
Super User

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

Thank you for your answer!

Could you send me the power bi file please?

 

Best regards,

 

Alberto

AlbLS
Frequent Visitor

@mussaenda 

 

Thank you for your answer!

Could you send me the power bi file please?

 

Best regards,

 

Alberto

Anonymous
Not applicable

@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

 

@AlbLS,

 

link is here.

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

 

@Anonymous,

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

Thank you, i really appreciate!

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

@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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.