Forum Discussion
Weighted average calculating weighting factor
wosscr -
Try the following Measure.
You'll need separate Date and Time Dimension tables, and use those tables for your filters/slicers. These tables will each need to have a Relationship with your Fact table.
Wtd Avg Calls =
var calls_selected = SUM(Calls[Calls])
var calls_squared = calls_selected ^ 2
var total_calls = CALCULATE(
SUM(Calls[Calls]),
ALLSELECTED(Calls)
)
return DIVIDE(calls_squared,total_calls)Cheers!
Nathan
Hi Anonymous,
Thanks for replying. I'm not sure if I understood what you proposed, I tried to make sense of it but it doesn't return what I was expecting.
To make it a little bit easier (maybe) here it a link to a pbix with some data.
It shows the "selected hours" (this can be ignored) and the "wtd avg calls" but really I've been thinking that doesn't make much sense for a measure to shows part the result if what I really need is the full result, that is the wtd average.
As an example, I select "March 25, 2019" from the Data picker and "9" and "10" from the Hour picker.
The wtd average should be: 10.71
Because what the code should do is take each hour and get the wtd factor, in this case "9" (9 / 21 = 0.43) and "10" (12 / 21 = 0.57) and then multiply each received calls of the hour to them sum it up (9 * 0.43 + 12 * 0.57 = 10.71).
Date Hour Received Calls Wtd Factor Wtd Avg 3/25/19 9 9 0.42857143 3.85714286 3/25/19 10 12 0.57142857 6.85714286 Any help is appreciated.
wosscr - Yes, I see that one didn't work. Sorry about that. Try this:
Wtd Avg Calls 2 = VAR calls_squared = SUMX(Table1, [Received Calls] ^ 2) VAR total_calls = CALCULATE(SUM(Table1[Received Calls]), ALLSELECTED('Table1'[Hour])) RETURN DIVIDE(calls_squared,total_calls)Hope this helps,
Nathan
