Forum Discussion
How to write DAX code that will dynamically group the bottom 20% of results for a Pie Chart?
I have a Pie Chart showing the percent/value breakdown of reported conditions from a customer. There are roughly 20 or so categories, with the bottom 8 or so being small amounts that comprise 20% of the chart.
My manager has asked me to group the bottom 20% into one category to reduce the clutter, while leaving the remaining 80% showing the full name of the reported condition.
I'm expecting this dataset to grow over time so I don't want to hard-code the 20% to be specific things in case one of the conditions becomes a big enough slice that it should be broken out on its own.
I'm not really great with Dax code yet, can anyone help me find a solution that will dynamically determine what conditions make up the bottom 20% and group them into an "Other" category so that my Pie Chart displays them together?
Edit: title to correctly be DAX code, not M code.
So I am NGL, I got a little lost trying to understand how to translate the article to suit my needs for a pie chart and percentages, but generally it seemed on point so I gave you a thumbs up. I'm almost ashamed to say...I went and asked ChatGPT a few questions and got a working solution for my situation. Edited for posting, here it is for posterity:
(1) Created a measure that counts my reported conditions:CondCalc = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Condition]))(2) Created calculated column to use in my pie chart:ConditionGroupBottom20 =VAR Threshold = PERCENTILEX.INC('Table', [CondCalc], 0.2)RETURNIF([CondCalc] >= Threshold,'Table'[Condition],"Others")
(3) Assigned the calculated column as the value/legend of my pie chart.This had the effect of grouping the bottom 20% of my pie chart slices into one categor "Others" while leaving the remaining slices with full details/name.
4 Replies
"M Code" and "dynamically" do not go together. You will have to do this in DAX.
Here is a walkthrough of what is required. Buckle up, it is not a simple ride.
So I am NGL, I got a little lost trying to understand how to translate the article to suit my needs for a pie chart and percentages, but generally it seemed on point so I gave you a thumbs up. I'm almost ashamed to say...I went and asked ChatGPT a few questions and got a working solution for my situation. Edited for posting, here it is for posterity:
(1) Created a measure that counts my reported conditions:CondCalc = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Condition]))(2) Created calculated column to use in my pie chart:ConditionGroupBottom20 =VAR Threshold = PERCENTILEX.INC('Table', [CondCalc], 0.2)RETURNIF([CondCalc] >= Threshold,'Table'[Condition],"Others")
(3) Assigned the calculated column as the value/legend of my pie chart.This had the effect of grouping the bottom 20% of my pie chart slices into one categor "Others" while leaving the remaining slices with full details/name.
That's actually cute, a nice way to abuse PERCENTILEX. You can mark your post as the solution.
Yeah thank you good catch. I'm still so new I mix up the vocabulary. Edited title post to correct. I will review your link and brb.
