topic Re: Grouping Pie Chart values under 2% as "Other" in DAX Commands and Tips

Grouping Pie Chart values under 2% as "Other"

Anonymous — Tue, 10 Oct 2023 10:27:46 GMT

So my data looks something like this:

flavor_ratio	Value
1	meat
2	chicken
7	veggies
10	chicken
9	meat

But of course, it goes for thousands of rows. Now when I want to visualise a pie chart, it would look something like this:

Meat 98%, veggies 1% and chicken 1%. I need it like this: Meat 98%, Others 2%. Basically everything under 2% should be grouped under others. How can I achieve this? I need it to respect any filters that I am applying as well.

Re: Grouping Pie Chart values under 2% as "Other"

DallasBaba — Tue, 10 Oct 2023 19:19:24 GMT

Hi Anonymous can you create a new column Percentage = DIVIDE([flavor_ratio], SUMX(TableName, [flavor_ratio])) * 100

Create another calculated column to group values under 2% as "Others."
Grouped Value = IF([Percentage] < 2, "Others", [Value])

create a pie chart visual.
Drag the "Grouped Value" column to the "Values" field well of the pie chart.

You can also drag the "Percentage" column to the "Values" field to display the percentages on the chart.

With these steps, your pie chart should display "Meat," "Chicken," and "Others" as categories based on the percentage threshold of 2%. This chart will also respect any filters you apply to your data.

You can customize your chart as needed, adding labels, titles, and legends to make it more informative.

Please let me know if this work

Thanks

Re: Grouping Pie Chart values under 2% as "Other"

Anonymous — Wed, 11 Oct 2023 11:20:13 GMT

Hi dallasbabatunde,
Thank you so much for this but I tried this already and it just gives me 100% others. I should note that most flavor ratios that i have are indeed below 1, like 0.something. So the new calculated column just shows others for all calculations.

Re: Grouping Pie Chart values under 2% as "Other"

DallasBaba — Wed, 11 Oct 2023 16:52:45 GMT

Anonymous Let's try another approach by creating a measure.

FlavorPercentage =

DIVIDE(

COUNTROWS(FILTER(FlavorData, FlavorData[Value] = EARLIER(FlavorData[Value]))),

COUNTROWS(FlavorData)

) * 100

Create another measure

FlavorCategory =

IF([FlavorPercentage] >= 2, [Value], "Others")

These will return "Others" for flavors with less than 2% and the flavor name for those with 2% or more.

Apply filters to the Pie Chart and see the result.

==========OR try another approach

FlavorPercentage2 =
COUNTROWS(FILTER(FlavorData, FlavorData[Value] = EARLIER(FlavorData[Value]))
) / COUNTROWS(FlavorData)

Drag a "Bar Chart" or "Column Chart" visualization to the canvas.
Add the "Value" field to the "Axis" or "Category" field well.
Add the "FlavorPercentage" measure to the "Values" field well.

You may want to customize the chart to make it more readable:

Sort the chart by percentage.
Adjust the axis labels and formatting.

================ OR try

Grouped Value = IF(SUM('Table'[flavor_ratio]) / SUMX(ALL('Table'), 'Table'[flavor_ratio]) < 0.02, "Others", MAX('Table'[Value]))

Reference :

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning