Forum Discussion
Grouping values together based on size
Hello,
I'm trying to group harbours together based on how much fuel they supply. An example is like this:
My data looks like this:
So using the example above, what I need is to:
group together harbors with a bin size 5 in summarized sizes together
color each bin seperately,
adding the percentages together for each group
summing the top 20 harbors together
summing the rest harbors together
9 Replies
v-xicaiCommunity Support
Hi 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.