Forum Discussion
Solved
Measure from data in one column
I have table: Line Indicator Value
A Efficiency 90
B Efficiency 80
A Weight 5
B Weight 10 I am trying to plot a metric that calculates the weighted average ...
Oleg222
Can you try this version?Weighted Average = DIVIDE( SUMX( VALUES(Table3[Date]), CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") * CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1") ) , SUMX( VALUES(Table3[Date]), CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1") ) )
Fowmy Your formula works, you helped me a lot. The last question - when calculating each indicator there is one more thing - the formula should count only when Efficiency1 <> 0. Tell me how to put a filter in your formula?
Oleg222
On the 25th I see one set having zero for both Efficiency and Weight
Do you want to exclude all the lines that have zero?
Fowmy No, I plan to delete the zeros altogether in the future. For example - 03/25/2021 Efficiency = 0 but Weight is not equal to zero and then the denominator will increase, although this should not be. My version of the formula - is there a better filtering option?
temp3 =DIVIDE(SUMX(VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") *
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1"),SUMX( VALUES('Сбор данных'[Date]),IF(CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1"), 1) *CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1"))Oleg222
Try this:Weighted Average = CALCULATE( DIVIDE( SUMX( VALUES(Table3[Date]), CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") * CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1") ) , SUMX( VALUES(Table3[Date]), CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1") ) ), Table3[Value] <>0 )