Weighted average calculation

[Jacodw0901]

Hi,

I would like to calculate the weighted average values on a matrix. This is a sample dataset:

UserId	CritID	CritSelVal	PoolCritID	PoolSelVal
1	3	0.20	4	5.00
1	2	0.14	5	7.00
1	2	0.20	4	5.00
1	2	0.14	3	7.00
1	1	0.20	5	5.00
1	4	7.00	5	0.14
1	1	0.14	4	7.00
1	3	5.00	5	0.20
1	1	5.00	3	0.20
1	1	5.00	2	0.20
1	4	5.00	3	0.20
1	5	7.00	2	0.14
1	4	5.00	2	0.20
1	3	7.00	2	0.14
1	5	5.00	1	0.20
1	5	0.14	4	7.00
1	4	7.00	1	0.14
1	5	0.20	3	5.00
1	3	0.20	1	5.00
1	2	0.20	1	5.00

This is the matrix view, using CritID and PoolCritID. Values in Matrix is the PoolSelVal

	1	2	3	4	5
1		5.000	5.000	0.143	0.200
2	0.200		0.143	0.200	0.143
3	0.200	7.000		0.200	5.000
4	7.000	5.000	5.000		7.000
5	5.000	7.000	0.200	0.143
Total	12.400	24.000	10.343	0.686	12.343

I have a problem calculating the weighted average in the table below as the calculation in the row is not the same calculation in the total column

Normalised based on PoolCritID (Column) Column Total used to calculate the normalised value

Weighted average should be the average for each row excluding the 0

	1	2	3	4	5	Weighted Average
1	0.000	0.208	0.483	0.208	0.016	0.23
2	0.016	0.000	0.014	0.292	0.012	0.08
3	0.016	0.292	0.000	0.292	0.405	0.25
4	0.565	0.208	0.483	0.000	0.567	0.46
5	0.403	0.292	0.019	0.208	0.000	0.23

Thanks

[Anonymous]

You can use HASONEVALUE to get a different result in the total.

For example, I used this for one of my averages.  Because I wanted my total to be the total of all the averages, not an average of all the averages.

IF(HASONEVALUE(Items[Item]),AvgCost , SUMX(VALUES(Items[Item]),AvgCost))

[negi007 · Accepted solution]

@Jacodw0901 

have you tried looking at the below solution

https://community.powerbi.com/t5/Desktop/Weighted-average-table-calculation/m-p/772223