total of Weighted Average not summing up

[YBZ]

Hi all,

I am stuck with a formula on how to calculate a weighted average. I see that there are already similar questions posted, but my issue is with more that I have it correct on a row level, but my total doesn't match with how I expect. 

My data source is a report that consists of total hours and total cost for every department. While the hourly rate is the total cost divided by the total hours. The aim is to have however one final weighted rate. 

1) First I created the rate per department that calculates the hourly cost

Rate = sumx('OC 23 (2)';'OC 23 (2)'[YTD actuals])/sumx('OC 23 (2)';'OC 23 (2)'[YTD QTY])*1000

2) Afterwards I created a measure to create the total hours as I had other active filters on the page

total = calculate(sum('OC 23 (2)'[YTD QTY]);ALL('OC 23 (2)'[Department]))

3) Weighted percentage is the hours per department, divided by the total hours

Weighted percentage = divide(sum('OC 23 (2)'[YTD QTY]);[total])

4) Weighted rate is the weighted percentage per department, multiplied with the Rate

Weighted rate = calculate([Rate]*[Weighted percentage])

Final results looks as followed : 

Department	YTD QTY	YTD actuals	Rate	total	Weighted percentage	Weighted rate
a	0	3000	Infinity	42552	0.00	NaN
b	0	415	Infinity	42552	0.00	NaN
c	0	6	Infinity	42552	0.00	NaN
d	29	45	1565	42552	0.00	1
e	88	554	6300	42552	0.00	13
f	45	40	880	42552	0.00	1
g	510	689	1352	42552	0.01	16
h	1585	2601	1641	42552	0.04	61
i	430	429	998	42552	0.01	10
j	718	1203	1675	42552	0.02	28
k	225	287	1276	42552	0.01	7
l	492	663	1349	42552	0.01	16
m	1508	1447	960	42552	0.04	34
n	4241	4870	1148	42552	0.10	114
o	104	213	2051	42552	0.00	5
p	5170	7953	1538	42552	0.12	187
q	0	5841	Infinity	42552	0.00	NaN
r	814	1738	2136	42552	0.02	41
s	3146	3647	1159	42552	0.07	86
t	1113	1059	952	42552	0.03	25
u	955	853	893	42552	0.02	20
v	157	368	2342	42552	0.00	9
w	86	85	984	42552	0.00	2
x	411	999	2431	42552	0.01	23
1	203	325	1599	42552	0.00	8
2	1689	1521	901	42552	0.04	36
3	3723	5053	1357	42552	0.09	119
4	10031	10617	1058	42552	0.24	250
5	143	169	1182	42552	0.00	4
6	712	1205	1693	42552	0.02	28
7	0	1586	Infinity	42552	0.00	NaN
8	0	199	Infinity	42552	0.00	NaN
9	0	274	Infinity	42552	0.00	NaN
10	0	252	Infinity	42552	0.00	NaN
11	165	591	3583	42552	0.00	14
12	344	852	2481	42552	0.01	20
13	1744	3164	1815	42552	0.04	74
14	73	242	3309	42552	0.00	6
15	129	622	4842	42552	0.00	15
16	0	262	Infinity	42552	0.00	NaN
17	0	727	Infinity	42552	0.00	NaN
18	0	1	Infinity	42552	0.00	NaN
19	0	0	Infinity	42552	0.00	NaN
20	66	91	1385	42552	0.00	2
21	105	146	1396	42552	0.00	3
22	382	425	1113	42552	0.01	10
23	165	213	1289	42552	0.00	5
24	915	1359	1485	42552	0.02	32
25	44	43	970	42552	0.00	1
26	32	67	2132	42552	0.00	2
27	66	73	1120	42552	0.00	2
28	0	-23	-Infinity	42552	0.00	NaN
	42552	69060	1623	42552	1	1623

Now the issue is that with the total of the column Weighted rate. It would normally need to sum up the totals of every row to come to the final result. I am however not sure on how to do it, therefore trying to get some assistance on the forum as I am in general struggling with totals in other reports 🙂 

Regards and thanks in advance,

[YBZ]

Thanks for prompt reply. I understand the formula. Now I would only need to delete the NaN values. Any idea how I can exclude them in my measure? 

[YBZ]

Manage to solve it with iferror. Thanks for the advice on the total , it works! 🙂

[amitchandak · Accepted solution]

@YBZ , The last one should be like

Weighted rate = sumx(Values(Table[Department]), calculate([Rate]*[Weighted percentage]))