Weighted Average DAX

[kirbynguyen]

Hello,

I am trying to use a measure to find the weighted average based on the values in another column per product. My data looks like this:

Product	Date	Value	WeightValue
A	1/1/2021	10	1
A	1/2/2021	20	2
A	1/3/2021	30	3
B	1/1/2021	40	4
B	1/2/2021	50	5
B	1/3/2021	60	6
C	1/1/2021	70	7
C	1/2/2021	80	8
C	1/3/2021	90	9

I want to visualize this in a line and the weighted average should be dynamic, so whenever I select 2 products, the weighted average would only take into account the products that are selected.

For ex, if I select Product A and Product C, the value I should get for 1/1/2021 is [10 * (1/(1 + 7))] + [70 * (7/(1 + 7))] = 62.5.

if I select Product B and Product C, the value I should get for 1/1/2021 is [40 * (4/(4 + 7))] + [70 * (7/(4 + 7))] = 59.091. 

Thanks for you help in advance.

[jdbuchanan71 · Accepted solution]

@kirbynguyen 

Give something like this a try, it's 3 measures but that keeps in clean in my mind.

1. Calculate all the weighted values

Weighted Values = SUMX ( 'Your Table', 'Your Table'[Value] * 'Your Table'[WeightValue] )

2. Get the sum of all the weights

Total Weights = SUM ( 'Your Table'[WeightValue] )

3. Get the weighted average value

Weighted Average Value = DIVIDE ( [Weighted Values], [Total Weights] )