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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Phoen1x09
Frequent Visitor

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.

1 ACCEPTED SOLUTION

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)
RETURN
IF(
    [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.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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

 

Showing the top 5 products and Other row - SQLBI

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)
RETURN
IF(
    [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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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