Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
Solved! Go to Solution.
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^!
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:
CLient list + Others = UNION( ALLNOBLANKROW(Projets[Client]), ROW("Client","Others"))
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êsHi 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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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^!
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:
CLient list + Others = UNION( ALLNOBLANKROW(Projets[Client]), ROW("Client","Others"))
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êsHi 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:
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êsMiguel,
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!
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êsHi 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |