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 for reference:

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

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?

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

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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'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