cancel
Showing results for
Did you mean:
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%.

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:

I leave you my Power BI file here:

Best regards

Alberto

1 ACCEPTED SOLUTION

Accepted Solutions
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" )`

10 REPLIES 10
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

Highlighted
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

Frequent Visitor

## Re: Group Smaller Slices with condition in Pie Charts

Hi natelpeterson,

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.

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

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

Frequent Visitor

## Re: Group Smaller Slices with condition in Pie Charts

Could you send me the power bi file please?

Best regards,

Alberto

Frequent Visitor

## Re: Group Smaller Slices with condition in Pie Charts

Could you send me the power bi file please?

Best regards,

Alberto

Super User

## Re: Group Smaller Slices with condition in Pie Charts

@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

Senior Member

## Re: Group Smaller Slices with condition in Pie Charts

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

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

Thank you, i really appreciate!

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)