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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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