Weighted Average Total Measure

[Anonymous]

I am working with weighted averages. I have read through the forums and found a lot of helpful answers about calculating the correct value at a row level and aggregate level. See the following posts for reference:

https://community.powerbi.com/t5/Desktop/Calculating-the-sum-of-dynamically-weighted-values/td-p/359...

https://community.powerbi.com/t5/Desktop/Weighted-average-incorrect-total/td-p/360009

However what I am now trying to do is slightly different. I’m wondering if it is possible to have the weighted average total (i.e. the aggregate number) show up on each of the individual rows (the more granular level), using a measure. Here is an example of what I would like to see.

I've tried using the SUMMARIZE functions and other methods but can't get things to roll up correctly. The reason I want it to be a measure is so that it is dynamic. That way if I slice the data the weighted averages will recalculate. 

*Note-I would be happy to provide sample pbix file however it appears newer forum members cannot post files? Any way to change this?

[v-cherch-msft]

Hi @Anonymous

It seems you need to use HASONEFILTER Function to create a measure like : IF(HASONEFILTER(Table[HoldingID]),[Total of DailyWeightedAvg]). If you need further help, you can upload the sample pbix file to OneDrive or Dropbox and post the link here.

Regards,

Cherie

[Anonymous]

Hi Cherie,

Here is a link to the sample file: https://www.dropbox.com/s/dbfumaz7jkau5dw/ForumSample.pbix?dl=0 

I tried using the COUNTROWS method used in the other forum posts. But I'm not sure how to create a [Total of DailyWeightedAvg] measure. In order to create that measure correctly you need the individual row context (to compute weighted values), but then you need to exlclude the row context to get the total, which is why I tried creating a virtual table using SUMMARIZE. However I can't figure out how to incorporate a column from the virtual table into my result. 

Thanks for your help

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may refer to below measure:

Total of DailyWeightedAvg =
SUMX (
    SUMMARIZE ( ForumData, ForumData[AsOfDate], "a1", [#DailyWeightedAvg] ),
    [a1]
)

#DailyWeightedTotal =
IF (
    HASONEFILTER ( ForumData[HoldingID] ),
    CALCULATE ( [Total of DailyWeightedAvg], ALL ( ForumData[HoldingID] ) )
)

Regards,

Cherie

[Anonymous]

Cherie thank you so much! The calculations are working as expected.