Forum Discussion
Grouping values together based on size
Community SupportHi Anonymous ,
You can try to create columns like DAX below.
Total quantity for Harbor= CALCULATE(SUM(Table1[Quantity]), FILTER(Table1,Table1[Harbor]=EARLIER(Table1[Harbor])))
Percentage for Harbor= Table1[Total quantity for Harbor]/CALCULATE(SUM(Table1[Quantity]),ALL(Table1))
Rank= RANKX(Table1,Table1[Total quantity for Harbor],,ASC,Dense)
Top 20 ports= CALCULATE(SUM(Table1[Total quantity for Harbor]), FILTER(Table1,Table1[Rank]>0&&Table1[Rank]<21))
Percentage for Top 20 ports= Table1[Top 20 ports]/CALCULATE(SUM(Table1[Quantity]),ALL(Table1))
Remaining ports= CALCULATE(SUM(Table1[Quantity]),ALL(Table1))- Table1[Top 20 ports]
Percentage for Remaining ports = Table1[Remaining ports]/CALCULATE(SUM(Table1[Quantity]),ALL(Table1))
For your demand "group together harbors with a bin size 5 in summarized sizes together, color each bin separately", I test it by the Conditional formatting function and refer to others , while it is not supported to implement in Power BI Desktop currently. But I have to agree that your demand is a good idea, you can post your new idea https://ideas.powerbi.com/forums/265200-power-bi-ideas , to improve the Power BI Desktop.
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you v-xicai for answering this.
I have a couple of questions though:
1. Did you test the columns out for yourself? When I try to run it using only columns what I get is just not the correct values
2. You only used columns in Power BI right? No measures
3. When I'm using this, I don't get as many harbors as I should get. Do you get that as well?
- v-xicai
Hi Anonymous ,
Sorry to make mistake for formula above. According to your sample data, I tested in desktop. Now, you can change the columns in DAX below(all the formula created in calculated Column), note that setting all columns Don't summarize.
Total quantity for Harbor = CALCULATE(SUM(Table1[Quantity]), FILTER(Table1,Table1[Harbor]=EARLIER(Table1[Harbor])))
Percentage for Harbor = Table1[Total quantity for Harbor]/SUM(Table1[Total quantity for Harbor])
Rank = RANKX(Table1,Table1[Total quantity for Harbor],,ASC,Dense)
Top 20 ports = CALCULATE(SUM(Table1[Quantity]), FILTER(Table1,Table1[Rank]>0&&Table1[Rank]<21))
Percentage for Top 20 ports = Table1[Top 20 ports]/SUM(Table1[Quantity])
Remaining ports = CALCULATE(SUM(Table1[Quantity]), FILTER(Table1,Table1[Rank]>20))
Percentage for Remaining ports = Table1[Remaining ports]/SUM(Table1[Quantity])
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW-mzV-WZUZImo1IQrJw9t4BC8w9_ErdQDjxiy0IxVa3Cw?e=jIPe6V
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you v-xicai,
I've now updated like you did but I keep getting an error.
I have copied everything like you did and made sure everyting is not summarized.
Can you see what the error could be? The above is how it should be and the second is my try
- v-xicai
Hi,
From your screenshot , find the two red part is from different field?
Best Regards,
Amy