cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hgalfre
Helper I
Helper I

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

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°]))
ClientNumber of projectsAmount
X110000
A350000
B15100000
C15000

 

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!
3 ACCEPTED SOLUTIONS

Hi Miguel! 

 

Here is the link to a sample data: 

https://we.tl/t-2pAc8JKZ3L

 

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^!

View solution in original post

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:

MFelix_0-1631728318842.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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! 😊

 

View solution in original post

10 REPLIES 10
hgalfre
Helper I
Helper I

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,

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi Miguel! 

 

Here is the link to a sample data: 

https://we.tl/t-2pAc8JKZ3L

 

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^!

View solution in original post

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:

MFelix_0-1631728318842.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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! 😊

 

View solution in original post

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.