Forum Discussion

JeremyCooper's avatar
JeremyCooper
New Member
10 years ago
Solved

Calculating the sum of dynamically weighted values

 

When calculating weighted values for a weighted average, how can we calculate a value which is the sum of those weighted values?

 

In the example below:

  • Total Quantity = SUM(Sheet1[Quantity])
  • Total Selected Quantity = CALCULATE(SUM(Sheet1[Quantity]),ALLSELECTED(Sheet1[Name]))
  • Proportion of Total Selected Quantity = [Total Quantity]/[Total Selected Quantity]
  • Avg X = AVERAGE(Sheet1[X])
  • Quantity Weighted Avg = [Proportion of Total Selected Quantity]*[Avg X]

What I want is to capture the sum of the Quantity Weighted Avg column in a value (which would be the quantity weighted average of all Name values selected).  This value is actually 7.73, but PowerBI is calculating it as SUM(Proportion of Total Selected Quantity)*Avg(X).

 

Would be optimal to calculate this using Direct Query.

 

Any ideas?

Need Help
  • Sean's avatar
    Sean
    10 years ago

    JeremyCooper we need to know how your data is set up in the source

     

    Here's another Measure which also worked for me - but again I don't know how your data is set up so... don't know if will work for you

     

    Quantity Weighted Average NEW =
IF (
    COUNTROWS ( VALUES ( 'Sheet1'[Name] ) ) = 1,
    [Proportion of Total Selected Quantity] * [Avg X],
    SUMX (
        VALUES ( 'Sheet1'[Name] ),
        [Proportion of Total Selected Quantity] * [Avg X]
    )
)

     

    See Picture...

     

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    10 years ago

    Try

    Quantity Weighted Avg =
SUMX (
    VALUES ( Sheet1[Name] ),
    [Proportion of Total Selected Quantity] * [Avg X]
)
  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    JeremyCooper If Owen's Measure gives you the right Total only - but not the right numbers per Name use this Measure instead

     

    I don't know how your data is set up so the condition may have to be adjusted... Let me know if this works. 

    Quantity Weighted Average =
IF (
    ISFILTERED ( 'Sheet1'[Name] ),
    [Proportion of Total Selected Quantity] * [Avg X],
    SUMX (
        VALUES ( 'Sheet1'[Name] ),
        [Proportion of Total Selected Quantity] * [Avg X]
    )
)
    • JeremyCooper's avatar
      JeremyCooper
      New Member
      10 years ago

      Owen's measure gives the correct value at the Total level (which meets the immediate need, fantastic work!).

       

      Sean's formula provides the correct numbers per Name but is showing the same simple average (9.62) at the Total level.

       

      I created this workbook from single sheet in excel to illustrate the problem, but I've got Owen's measure to work correctly on the (more complicated) real data without a hitch.

       

      The initial need has been met (thanks again!) but it would be great to combine the two into a single formula......also, I see this will only work in Import mode, which is fine, but curious if this type of calcualtion capability is something to be released in the near future as part of the Direcy Query toolbox?

       

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        10 years ago

        JeremyCooper we need to know how your data is set up in the source

         

        Here's another Measure which also worked for me - but again I don't know how your data is set up so... don't know if will work for you

         

        Quantity Weighted Average NEW =
IF (
    COUNTROWS ( VALUES ( 'Sheet1'[Name] ) ) = 1,
    [Proportion of Total Selected Quantity] * [Avg X],
    SUMX (
        VALUES ( 'Sheet1'[Name] ),
        [Proportion of Total Selected Quantity] * [Avg X]
    )
)

         

        See Picture...