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") ) )
Oleg222
Created the measure, please check with your data:
Weighted Average =
var __num =
SUMX(
FILTER( Table3 , Table3[Indicator] = "Efficiency" ),
var __line = Table3[Line]
var __value = Table3[Value]
return
CALCULATE(
SUM(Table3[Value]),
FILTER(
Table3,
Table3[Indicator] = "Weight" && Table3[Line] = __line
)
) * __value
)
var __den =
SUMX(
FILTER( Table3 , Table3[Indicator] = "Efficiency" ),
var __line = Table3[Line] return
CALCULATE(
SUM(Table3[Value]),
FILTER(
Table3,
Table3[Indicator] = "Weight" && Table3[Line] = __line
)
)
)
return
DIVIDE( __num , __den )
Fowmy Your measure almost works, with one exception - when on the same day for the indicator "Efficiency"" there is the same indicator (but it is zero), when calculating the denominator ( var __den), the total sum per day is doubled. Please tell me how to fix it?
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
Can you reshare the sample data with this zero scenario with the expected results as well?