Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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: gr...
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

     

     

    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's avatar
      v-xicai
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi,

       

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

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Best Regards,

      Amy

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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?