Weighted average calculating weighting factor

[wosscr]

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.

[Anonymous]

@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

[wosscr]

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.

[Anonymous]

@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