Calculation of weighted average by multiple groups

[Mercator_1980]

Dear all,

an excel has been imported to Power BI Desktop which contains the results of webdata per day.  Some of the values in column "Item" have been labeled falsely and where treated via Power BI "Edit Queries" --> "Transform" to standardize the itemnames. By this we have identical rows for Producers, Item, Category and date for which outside of power bi means have already been calculated!

See this example file picture:

 

The target now is to calculate the average of the price. This average must be weighted according to the "count" table per:
- Producer
- Item
- Category
- Date

I have tried quickmeasures but i can only define one Category (Group)  in the "Weigthed average per category" option.

I also tried to manipulate the Formular which was automatically created to accept more groups but i failed (see here the unedited version - i tried to manually add "VAR" - but could not extend the "KEEPFILTERS"):

Weighted Average =
VAR __CATEGORY_VALUES = VALUES('Table'[Producer])
RETURN
 DIVIDE(
  SUMX(
   KEEPFILTERS(__CATEGORY_VALUES);
   CALCULATE(
    SUM('Table'[Price])
     * SUM('Table'[count])
   )
  );
  SUMX(
   KEEPFILTERS(__CATEGORY_VALUES);
   CALCULATE(SUM('Table'[count]))
  )
 )

How can i calculated the weighted average in power bi for multiple groups?

Thank you

[v-chuncz-msft · Accepted solution]

@Mercator_1980,

You just need to use measure below.

Measure =
DIVIDE (
    SUMX ( Table1, Table1[count] * Table1[price] ),
    SUMX ( Table1, Table1[count] )
)

[syedallam]

Hi,

I tried your solution and I am able to get the data over a table value.  Now, what I am trying to do is, us this weighted average to show as line series.  For eg., I have total trades (buy and sell) for multiple currencies (USD, GBP, EUR) and for each month (APR, MAY, JUN).  I have used measure to calculate weighted average rate of these 3 categories. I want to create a Line and clustered column chart with the values shown along with the measure value as a line value.  When I try doing it, it show the average or total of the measure value.

Please help me out.

[Mercator_1980]

@v-chuncz-msft

Thank you - this is working and giving me a weighted average by multiple groups. Manual calculations are identical to those of power bi.

But i truly have problems understanding the functionality of measures and the formula.

It is unclear to me, how the measure observes the different groups in its calculation! I understand, that the measure will be influenced by the Filters i am using like date, item and so on. But there must be some interconnection with the SUMX-command.

The tooltip to SUMX states:

"Returns the sum of an expression evaluated for each row in a table"

Is ist therefore correct to say that the measure calculates the number (Sum) of identical rows (identical item, dates and so on) per each individual combination of all rows taken into consideration? By this we get the sum of occurences per combinationen. This is what SUMX ( Table1, Table1[count] ) is about. The Dividision of SUMX ( Table1, Table1[count] * Table1[price] ) by SUMX ( Table1, Table1[count] ) is therefore also done for each individual combination of grouping combinations.

That still leaves me with not understanding, how the Measure is knowing to calculate (sum up) the overall weighted average.

Where does my brain break?