Calculating the sum of dynamically weighted values

[JeremyCooper]

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?

[Sean]

@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]

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 · Accepted solution]

@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...

[JeremyCooper]

This does it, thanks Sean!  I've tweaked it for the real data and it's perfect.

The source data for the example at hand looks like this:

[OwenAuger]

Try

Quantity Weighted Avg =
SUMX (
    VALUES ( Sheet1[Name] ),
    [Proportion of Total Selected Quantity] * [Avg X]
)