Search code examples
powerpivotdaxpowerquery

Advice needed on weighted averaging in power query or power pivot

I need to create a weighted average that multiplies a column of volume manufactured for multiple manufacturing plants by a column containing the cost to manufacture at each plant, and returns one weighted average value for a specific product type for all plants.

I've tried adding this as a calculated column using:

=sumx('Plant','Plant'[Cost]*'Plant'[Tonnage])/sum('Plant'[Tonnage])

But this goes row by row, so it doesn't give me the full over riding average that I need for the company. I can aggregate the data, but really want to see the average lined up against individual plant for benchmarking

Any ideas how I can do this?

[1]

Solution

  • You can do this in multiple ways. You can either make a single more complex calculation, or you can make a few calculated columns to make the final calculation more transparent. I will pick the latter approach here, because it is more easy to show what is going on. I'm going to use the following DAX functions: CALCULATE, SUM, and ALLEXCEPT.

    First, create three new calculated columns. The first one should contain the [Volume] times [Cost] for each record:

    VolumeTimesCost:=[Volume] * [Cost]

    The second one should contain the sum of [VolumeTimesCost] for all plants within a given product type. It could look like this:

    TotalProductTypeCost:=CALCULATE(SUM([VolumeTimesCost]),ALLEXCEPT([Product Type]))

    Using the ALLEXCEPT([Product Type]) removes the filter from all other columns than the [Product Type] column.

    The third calculated column should contain the SUM of [Volume] for all plants within a given product type. It could look like this:

    TotalProductTypeVolume:=CALCULATE(SUM([Volume]),ALLEXCEPT([Product Type]))

    You can then create your measure based on the two calculated columns [TotalProductTypeCost] and [TotalProductTypeVolume].

    I hope that helps you solve the issue correctly. Otherwise feel free to let me know!