Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Weighted Average Total Measure

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 ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

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