cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jgu100 Frequent Visitor
Frequent Visitor

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:

20190524_093810.jpg

 

My data looks like this:

A.PNG

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 9
v-xicai New Contributor
New Contributor

Re: Grouping values together based on size

Hi @jgu100 ,

 

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.

jgu100 Frequent Visitor
Frequent Visitor

Re: Grouping values together based on size

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 New Contributor
New Contributor

Re: Grouping values together based on size

Hi @jgu100 ,

 

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

 

3.png

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW-mzV-WZUZImo1IQr...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

jgu100 Frequent Visitor
Frequent Visitor

Re: Grouping values together based on size

 

 

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

Capture.PNG

Highlighted
v-xicai New Contributor
New Contributor

Re: Grouping values together based on size

Hi,

 

From your screenshot , find the two red part is from different field?

 

4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

jgu100 Frequent Visitor
Frequent Visitor

Re: Grouping values together based on size

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 New Contributor
New Contributor

Re: Grouping values together based on size

Hi @jgu100 ,

 

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

jgu100 Frequent Visitor
Frequent Visitor

Re: Grouping values together based on size

jgu100 Frequent Visitor
Frequent Visitor

Re: Grouping values together based on size

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