Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
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?
Solved! Go to 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 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] ) )
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?
@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...
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |