Wanting to group smaller pie slices together to unclutter the view

[ChrisAZ]

I had seen some previous questions around this, but could not find a solution that seemed to match or meet what I am hoping to do. I would like to be able to have Service Tasks that are say under 3% get grouped into an "Other" group. These will vary from individual and time period so it needs to be dynamic in that way. What might be the most logical way to do this? Thanks!

[ChrisAZ]

Wondering what protocol is on these forums if you don't get a solution that seems to work from a post and other individuals stop responding. Is it OK to start a new thread looking for assistance and indicate what I have already tried?

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[danextian]

Hi @ChrisAZ 

If you want it dynamic such that you use use Other in the legend, use a disconnected table has a column containing the distinct tasks and the word other.

Write a measure that temporarily stores the percentage for each distinct tasks, add a column that returns other if the percentage <3% and use that column to filter the temporary table.

Sales_Other =
VAR SubcatOther =
    VALUES ( SubcategoryOther[Subcategory] )
VAR _tbl =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            'Product'[Subcategory],
            "@Sales", [Sales],
            "@Sales%", [Sales %]
        ),
        "@Subcategory", IF ( [@Sales%] < 0.03, "Other", [Subcategory] )
    )
VAR _filtered =
    FILTER ( _tbl, [@Subcategory] IN SubcatOther )
RETURN
    SUMX ( _filtered, [@Sales] )

Please see the attached sample pbix.

[Kedar_Pande]

@ChrisAZ 

Create a Measure

Task Percentage = 
DIVIDE(
SUM('Table'[TaskValue]),
CALCULATE(SUM('Table'[TaskValue]), ALL('Table'[Service Task]))
)

Create a New Column

Grouped Task = 
IF(
[Task Percentage] < 0.03,
"Other",
'Table'[Service Task]
)

Replace the original Service Task field in your visuals with the Grouped Task column. This will dynamically combine smaller tasks into an "Other" category.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[ChrisAZ]

Thank you. What do I replace the "TABLE" portion with?

[Anonymous]

Thanks for the reply from olgad  and Kedar_Pande , please allow me to provide another insight:

Hi,@.
Thanks for reaching out to the Microsoft fabric community forum.

Based on your requirements, I have proposed three solutions:

I created the following test data:

1.Firstly, the calculated table you mentioned:

other = 
VAR ta1 =
    SUMMARIZE (
        'Table',
        'Table'[TaskName],
        'Table'[Value],
        "percent", DIVIDE ( 'Table'[Value], CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) ) )
    )
RETURN
    FILTER ( ta1, [percent] < 0.03 )

Here is the final result:

2.Secondly, measures:

MEASURE =
VAR Task_Percentage1 =
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) )
    )
RETURN
    IF ( Task_Percentage1 < 0.03, "Other", MAX ( 'Table'[TaskName] ) )

Here is the final result:

3.Lastly, calculated columns:

Grouped Task =
VAR Task_Percentage =
    DIVIDE ( 'Table'[Value], CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) ) )
RETURN
    IF ( Task_Percentage < 0.03, "Other", 'Table'[TaskName] )

Here is the final result:

You can choose any of these solutions based on your needs.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisAZ]

So I want to try one of these solutions, but I noticed all of the suggestions seem to be using the DIVIDE approach to get to a percentage. I do not need to calculate the percentages, I already have them showing in the pie chart for each Task. I really just need to create a way to lump certain ones that make up less than a certain % into one "Other" group.

[olgad]

First calculate the percentage:

TaskPercentage =
DIVIDE(SUM('ServiceTasks'[Value]), calculate(SUM('ServiceTasks', 'ServiceTasks'[Value]), Allselected('ServiceTasks')) 

Then:

TaskCategory =
IF(
[TaskPercentage] < 0.03,
"Other",
'ServiceTasks'[TaskName]
)

[ChrisAZ]

Pardon my ignorance, so I do these as Calculated Tables or measures?

[olgad]

The first one is a measure, the second is a column

[ChrisAZ]

Thanks. So I was able to create this measure and the column, but the issue is that it is doing the calculation based on each individual task in my list, and I need it to be against the total of each of those tasks in a displayed period of time. For example, the ones listed as Freight Truck PM will be aggregated to a total for that service task in the pie chart. Only when the overall percent to that task is less than 3% do I want it to be put into "Other"

[ChrisAZ]

@olgad Hi there, was just wondering if you saw my follow up and had any additional ideas?

[danextian]

Hi @ChrisAZ 

Just checking if you have tried the approach in my initial response? That does dynamically aggregate items to others based on certain conditions.

[ChrisAZ]

No, I am sorry but I am unable to follow your suggested solution so I have been trying this other way.

[danextian]

There is a sample pbix in my initial post. If you want certain categories to appear as other in your pie chart based on certain conditions, you will need to use a physical table to materialize the result. You can't use measures as they cannot be used as dimensions while a calculated column approach will not be dynamic.

[ChrisAZ]

Thanks. So I did give this a try. I was able to create the corresponding Table, but it won't accept my syntax for the Measure and I am not sure what I have incorrect. I am obviously modifying the content you shared to fit my data/fields.

[ChrisAZ]

@danextian Wondering if you had a chance to review this latest result? Thanks!

[ChrisAZ]

Also open to other people's input if they have any ideas on how to get this to the finish line. Thanks!

[olgad]

Hi, I see your problem.
Please, adjust your measure for 

TaskPercentage =

DIVIDE( calculate(SUM('ServiceTasks'[Value]), ALLEXCEPT('ServiceTasks', ServiceTasks[TaskName])), calculate(SUM('ServiceTasks'[Value]), Allselected('ServiceTasks')))

Pie chart shows you the % automatically, but in order to label something as other, you need to calculate those percentages. This formula will do exactly that. 
Before:
 

After: 

[ChrisAZ]

I redid the calculation, but where do I add it for the pie chart? I can't seem to get it to display properly.