Grouping values together based on size

[Anonymous]

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

[v-xicai]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

Thank you,

of coure haha.

But I also found out with this solution that if you are trying to filter on dates for example or something else Power BI doesn't update.

Do you have any idea how to solve that?

[v-xicai]

Hi @Anonymous ,

>>But I also found out with this solution that if you are trying to filter on dates for example or something else Power BI doesn't update.

It is referred to a new demand about date, right? I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[Anonymous]

Here is a demo of my report @v-xicai :

https://drive.google.com/file/d/1jsj5QORZCaXur-6C8z0x3FAtMEBTZtNB/view?usp=sharing

Thank you

[Anonymous]

@v-xicai did you manage to open the report?