Forum Discussion
Weighted Average DAX
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.
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] )
1 Reply
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] )