Forum Discussion

Dellis81's avatar
Dellis81
Post Prodigy
4 years ago
Solved

Weighted Average

Hello - I am needing help with a weighted average calculate across multiple combination of categories.   This is a first attempt, utilizing PBI quickmeasures - unfortunately only allows one category ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You need to iterate at the lowest level of granularity for it to work properly at all levels.

     

    In this case, you can iterate at the table row level to keep things simple.

     

     

    Avg Moisture =
DIVIDE (
    SUMX ( 'Table', [SumMoisture] * [WetBushels] ),
    SUMX ( 'Table', [WetBushels] )
)

     

    Note that this gives 14% for both numbers circled. The value 20.87% makes no sense when all of the moisture measurements are below 16%.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You need to iterate at the lowest level of granularity for it to work properly at all levels.

 

In this case, you can iterate at the table row level to keep things simple.

 

 

Avg Moisture =
DIVIDE (
    SUMX ( 'Table', [SumMoisture] * [WetBushels] ),
    SUMX ( 'Table', [WetBushels] )
)

 

Note that this gives 14% for both numbers circled. The value 20.87% makes no sense when all of the moisture measurements are below 16%.

  • Dellis81's avatar
    Dellis81
    Post Prodigy
    4 years ago

    Thank you very much.   After all my google searches, your solution is so elegant.   Apologize on the 20% number, I was working off a spreadsheet, and had a wrong number typed in.

     

    Thank you!