Forum Discussion
Weighted average calculating weighting factor
Hey guys,
I'm having a hard time creating a measure that filters rows and I could use some help.
The data I have looks like this
Hour | Calls |
9 | 9 |
10 | 12 |
11 | 14 |
12 | 23 |
13 | 38 |
14 | 22 |
15 | 23 |
16 | 25 |
17 | 28 |
So I need a formula that gives me a weighted average, but using a weighting factor. So it's different steps, number one calculating the weighting factor:
Hour | Calls | Weighting Factor |
9 | 9 | 0.046391753 |
10 | 12 | 0.06185567 |
11 | 14 | 0.072164948 |
12 | 23 | 0.118556701 |
13 | 38 | 0.195876289 |
14 | 22 | 0.113402062 |
15 | 23 | 0.118556701 |
16 | 25 | 0.128865979 |
17 | 28 | 0.144329897 |
9 | 194 | 1 |
The weighting factor is calculated by the total calls of each hour divided by the total calls for the selected hours.
The second part is getting the actual average by multiplying the weighting factor by the number of calls and summing the results as shown here:
Hour | Calls | Weighting Factor | Wtd Avg Calls |
9 | 9 | 0.046391753 | 0.417525773 |
10 | 12 | 0.06185567 | 0.742268041 |
11 | 14 | 0.072164948 | 1.010309278 |
12 | 23 | 0.118556701 | 2.726804124 |
13 | 38 | 0.195876289 | 7.443298969 |
14 | 22 | 0.113402062 | 2.494845361 |
15 | 23 | 0.118556701 | 2.726804124 |
16 | 25 | 0.128865979 | 3.221649485 |
17 | 28 | 0.144329897 | 4.041237113 |
9 | 194 | 1 | 24.82474227 |
So for this specific day, selecting those specific hours, the result would be 24.82. In other words, I need this measure needs to work with the selected hours, but also considering dates, so that I can drill down from month, week, day (date and hour are separate tables)
I tried to CALCULATE using ALLSELECTED but it will give me the total calls of all the dates selected instead of the total calls for each day in the selected hours. I honestly don't know how to combine the date and the hour in a way that I can create a measure that gives me the Total Calls for the selected hours each day, week, month, or whatever data range I am reviewing.
Any guidance on this is much appreciated.
3 Replies
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
