Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Dynamic "Other" category in pie chart with a %...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dynamic "Other" category in pie chart with a % threshold

09-08-2021
02:40 AM

Hi there!

I'm trying to create 2 chart pies that show data from some clients; 1 will depend on the amount of turnover of each client, the other one one the number of projects we have with the each client.

There are many clients, so I want to regroup in an "other" field the ones that, for each value, are under 4% (of the total turnover for the 1st pie, and of the total number of projects for the second one).

Obviously, I need this "Other" group to change depending on the data (maybe one small client will make a large order and become top client by the end of the year). So manually grouping is not a long-term option.

I have found this topic with a good explanation:

https://community.powerbi.com/t5/Desktop/How-to-show-TopN-quot-Other-quot-Group/td-p/89781

However, I'm struggling with the % part, as this case doesn't work with a percentage.

So, so far I have this new table, created with the DAX function on the above-mentioned conversation:

Groups = SUMMARIZE(Projets,Projets[Client],"Amount",SUM(Projets[Montant]),"Number of projects",DISTINCTCOUNT(Projets[N°]))

Client | Number of projects | Amount |

X | 1 | 10000 |

A | 3 | 50000 |

B | 15 | 100000 |

C | 1 | 5000 |

So far I've tried only for the amount column, not the projects. I assume that I will need to use the same process again for the number of projects. I don't think I can do something that could work for both columns differently.

I was thinking about adding a column with an equivalent of what is mentioned in the post: NewGroupName = IF(RANKX(FILTER('Table','Table'[Type]=EARLIER('Table'[Type])),'Table'[TotalAmount])<=2,'Table'[GroupName],"Others")

So I wrote this:

Client group = IF(RANKX(FILTER('Groups','Groups'[Client]=EARLIER('Groups'[Client])),('Groups'[Amount]/SUM('Groups'[Amount])*100)<=0.04),'Groups'[Client],"Other")

=>But it wouldn't work, it lists all the client names, no "Other" appears.

So i tried to create a measure for the total of the Amount column:

Measuretotalamount = SUM('Groupes donneurs ordre'[Amount])

>Which seems to be working, except that if I try to add it to the formula for the Client group:

Client group = IF(RANKX(FILTER('Groups','Groups'[Client]=EARLIER('Groups'[Client])),('Groups'[Amount]/[Measuretotalamount]*100)<=0.04),'Groups'[Client],"Other")

=>There's an error: circular dependency detected

I've tried also to create a column that would give me the % for each client:

%amount = 'Groups'[Amount]/[Measuretotalmontant]

=>but it doesn't work either, I get 100% for each row, except for the ones with "0", to which I get a NaN.

I guess I'm just coding badly, but I need your help 😀

Thank you in advance!

Solved! Go to Solution.

3 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-15-2021
06:46 AM

Hi Miguel!

Here is the link to a sample data:

You'll see i've kept it simple, and reproduced all the steps mentioned in my question:

- created a summarized table named Groups

- created a column (Client group) that tries to report "Others" for values below 4% of the total (in that case, the number of projects), which only replicates the name of the client (all clients below 19 projects should have "others" written in the column)

-created a measure to calculate the total of the column Amount, and tried to use it again with the formula from column "Client group" in the column "Client group2", but this time with the amount >which creates a circular dependency

-and tried to create a column that would calculate what each client represents in % of the total amount, but it gives me "1" for each row

Thanks again for your help^!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-15-2021
10:52 AM

Hi @hgalfre ,

I was making some investigation and got to a different solution, this can then be adjusted for each of the columns you need just by changing the amount or the projects:

- Create a calculated table with the client and a line others:

`CLient list + Others = UNION( ALLNOBLANKROW(Projets[Client]), ROW("Client","Others"))`

- Make a relationship between this table and your projects table
- Add the following measures:

```
Percentage =
DIVIDE (
SUM ( Projets[Amount] ),
CALCULATE (
SUM ( Projets[Amount] ),
ALLSELECTED ( 'CLient list + Others'[Client] )
)
)
Percentage + others =
IF (
SELECTEDVALUE ( 'CLient list + Others'[Client] ) = "Others",
0,
IF ( [Percentage] >= 0.04, [Percentage] )
)
Final Percentage + Others =
IF (
SELECTEDVALUE ( 'CLient list + Others'[Client] ) = "Others",
1 - SUMX ( ALLSELECTED ( 'CLient list + Others'[Client] ),[Percentage + others]),
[Percentage + others]
)
```

Result below and in attach file:

Some adjustments of this can be made by making the % of the threshold dynamic or also the column that is picked up for the calculations (amount / number of projects).

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-16-2021
06:06 AM

Hi Miguel!

Thank you for your time.

It seems to be working well. 😀

I must admit though I have trouble understanding how the function below actually works;

For instance, this one seems to me that it divides the total of the column amount (SUM (Projets[Amount]) by the same total (SUM (Projets[Amount]) (after removing filters):

I've tried to use it for a different case, but it wouldn't work. I've checked the explanation online by Microsoft, but really couldn't understand it (in case you may help with this, I'll gladly read your explanation😅).

Percentage = DIVIDE ( SUM ( Projets[Amount] ), CALCULATE ( SUM ( Projets[Amount] ), ALLSELECTED ( 'CLient list + Others'[Client] ) ) )

Anyway, while decomposing it, I thought I should use the DIVIDE function to repair my % calculation column, but without one of the two SUMs, and it did work:

Percentage =

DIVIDE (

Groups[Amount] ,

SUM ( Groups[Amount] )

)

So I fixed the %amount columns I had tried to write. Then I wrote a basic IF formula to classify into "Other" the clients with less than 4% amount :

Client group = IF('Groups'[Percentage]<=0.04,"Other",'Groups'[Client])

It works fine as well!

Eventually, I used those two colums to display my chart pie, as I thought it was quicker with less columns (and more usable ones).

Thanks again for your help and time! 😊

10 REPLIES 10

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-14-2021
05:30 AM

Hi Miguel,

i'm sorry, I don't understand your question; "eliece"?

I've written all of my calculations code in my original post. My problem lies with all of my tentatives to write measure or calculated columns, I get an error for each.

Thanks for your help,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-15-2021
02:11 AM

Hi @hgalfre ,

Is it possible to please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-15-2021
06:46 AM

Hi Miguel!

Here is the link to a sample data:

You'll see i've kept it simple, and reproduced all the steps mentioned in my question:

- created a summarized table named Groups

- created a column (Client group) that tries to report "Others" for values below 4% of the total (in that case, the number of projects), which only replicates the name of the client (all clients below 19 projects should have "others" written in the column)

-created a measure to calculate the total of the column Amount, and tried to use it again with the formula from column "Client group" in the column "Client group2", but this time with the amount >which creates a circular dependency

-and tried to create a column that would calculate what each client represents in % of the total amount, but it gives me "1" for each row

Thanks again for your help^!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-15-2021
10:52 AM

Hi @hgalfre ,

I was making some investigation and got to a different solution, this can then be adjusted for each of the columns you need just by changing the amount or the projects:

- Create a calculated table with the client and a line others:

`CLient list + Others = UNION( ALLNOBLANKROW(Projets[Client]), ROW("Client","Others"))`

- Make a relationship between this table and your projects table
- Add the following measures:

```
Percentage =
DIVIDE (
SUM ( Projets[Amount] ),
CALCULATE (
SUM ( Projets[Amount] ),
ALLSELECTED ( 'CLient list + Others'[Client] )
)
)
Percentage + others =
IF (
SELECTEDVALUE ( 'CLient list + Others'[Client] ) = "Others",
0,
IF ( [Percentage] >= 0.04, [Percentage] )
)
Final Percentage + Others =
IF (
SELECTEDVALUE ( 'CLient list + Others'[Client] ) = "Others",
1 - SUMX ( ALLSELECTED ( 'CLient list + Others'[Client] ),[Percentage + others]),
[Percentage + others]
)
```

Result below and in attach file:

Some adjustments of this can be made by making the % of the threshold dynamic or also the column that is picked up for the calculations (amount / number of projects).

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-16-2021
06:06 AM

Hi Miguel!

Thank you for your time.

It seems to be working well. 😀

I must admit though I have trouble understanding how the function below actually works;

For instance, this one seems to me that it divides the total of the column amount (SUM (Projets[Amount]) by the same total (SUM (Projets[Amount]) (after removing filters):

I've tried to use it for a different case, but it wouldn't work. I've checked the explanation online by Microsoft, but really couldn't understand it (in case you may help with this, I'll gladly read your explanation😅).

Percentage = DIVIDE ( SUM ( Projets[Amount] ), CALCULATE ( SUM ( Projets[Amount] ), ALLSELECTED ( 'CLient list + Others'[Client] ) ) )

Anyway, while decomposing it, I thought I should use the DIVIDE function to repair my % calculation column, but without one of the two SUMs, and it did work:

Percentage =

DIVIDE (

Groups[Amount] ,

SUM ( Groups[Amount] )

)

So I fixed the %amount columns I had tried to write. Then I wrote a basic IF formula to classify into "Other" the clients with less than 4% amount :

Client group = IF('Groups'[Percentage]<=0.04,"Other",'Groups'[Client])

It works fine as well!

Eventually, I used those two colums to display my chart pie, as I thought it was quicker with less columns (and more usable ones).

Thanks again for your help and time! 😊

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-16-2021
06:52 AM

Hi @hgalfre ,

First of all my approach is based on a dinamic approach and without any additional columns on the model, the only addition to the model is the table with the list of customers + the others value.

I'm using measures that is why I use the SUM and the ALLSELECTED that will allow to have the calculation done by the aggregation at whatever level you need.

If we do the break out of the formula what I'm doing is the following:

```
Percentage =
DIVIDE (
// Sum of the amount of the selected client projects (only the ones that are within the context of the chart and the specified client row or legend
SUM ( Projets[Amount] ),
//Sum of the amount for all clients on the visualization no matter of the "row" / legend context
CALCULATE (
SUM ( Projets[Amount] ),
ALLSELECTED ( 'CLient list + Others'[Client] )
)
)
```

Looking at the comments above what I'm doing is the following:

A - 10

B - 10

C - 10

Total - 30

For the first part of the divide I'm getting the individual rows 10 (for each one), on the second part I'm getting the Total line for both of them so 30. Final result for each line will be

A - 10 - 33,333%

B - 10 - 33,333%

C - 10 - 33,333%

Using the ALLSELECTED forces the values of the 30 to be consider so if you add or take values from the customer list you will get the correct calculation always based on the current selection. Assuming you make a filter and only have A and B the result would be:

A - 10 - 50% = 10/20

B - 10 - 50% = 10/20

The rest of the measures are need because of the context transiction that will allow to make the filters and calculations has you need because if you applied it to a single measure the result would break on context transiction and would always return 100%.

If I'm not clear please tell me, and I can try to explain better.

In your case since you are using a table to make your chart this will not be dinamic so if you start to apply filters on your data the results can be incorrect.

But if it works for your purpose glad I could help out to guide you in the right direction.

Don't forget to mark your answer as the solution so it can help others.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-16-2021
07:15 AM

Miguel,

Thanks it's much more clear!

Sorry for my mistake, it's true you used measures, not columns (the table visual confused me). But in terms of quantity, it does create 1 table + 3 measures, when my solutions uses 1 table + 2 columns that can be reused.

However, you're right, I noticed that the data wouldn't change, as I had to apply a filter on top, but I filtered from the table creation. This data doesn't need to be dynamic for now, I'll see to improve it with your solution if asked by my colleagues 😀

Thanks again for your help! All the best!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2021
07:41 AM

Hi @hgalfre ,

Check this detailed explanation about how to create the dinamic others part then you can use in any chart.

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2021
06:53 AM

Hi Felix,

Thanks for your answer.

Unfortunately, the example doesn't help, as the blogger doesn't give the measure to calculate a % for each row (he just inserts "[selected measure]" as the location to insert our own measure, and mine doesn't work (I get 100% for each row when i try to calculate the % of the total of the column)).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2021
09:18 AM

Hi @hgalfre,

Your issue is with the percentage?

Can you share how you are calculating eliece is just an adjustment based on all or all selected that maybe needed.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português
Featured Topics

Top Solution Authors

User | Count |
---|---|

241 | |

152 | |

71 | |

62 | |

42 |

Top Kudoed Authors

User | Count |
---|---|

248 | |

191 | |

90 | |

67 | |

57 |