Forum Discussion

Christian_Houen's avatar
Christian_Houen
Frequent Visitor
8 years ago
Solved

Calculation of weighted average by multiple selected groups

Hi

 

In close resemblence to this request I would love some help on how to calculate an average of multiple selected groups.

Say I have the following data:


I want the weighted average of the price grouped by 1,2,3 (so I can use it for drill down on my graph).

Issue is that I have many more columns which I do not want to be part of the grouping

 

What could be a solution?

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Christian_Houen,

     

    What do you mean "grouped by 1,2,3" ? And what do you mean "I have many more columns which I do not want to be part of the grouping"? Which columns are not wanted? Please show us your desired output.

     

    Regards,

    Yuliana Gu

    • Christian_Houen's avatar
      Christian_Houen
      Frequent Visitor
      8 years ago

      Hi Yuliana

       

      I'll try and expand my mock data table a little bit.


       

      So, my desired output is the weighted average price (with formula shown).

      My firm may sell multiple of the same item on the same purchase order at different prices in the same country and in the same region.

      I want to get an average for each product in each country and each region. I included Russia in both Europe and Asia as the real data is structured in the way that the value in secondary column may appear in relation to all values in the first column.

      So weighted average price by item ("Name"), by ("Country") and by ("Region"). I do however have more data columns ("Column A") and ("Column B") and many more, which I do not want to be included in the grouping. E.g. I don't want the average price by Name, Country, Region and Column A (or any other column).

      Let me know if I still do not explain this clearly enough.