Forum Discussion

Oleg222's avatar
Oleg222
Helper II
5 years ago
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 ...
Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    5 years ago

    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")
    )    
)

     

FrankAT's avatar
FrankAT
Community Champion
5 years ago

Hi Oleg222 ,

you can do it with DAX like this:

 

 

Weighted Average = VAR _Table =
    SUMMARIZE (
        'Table',
        'Table'[Line],
        "Efficiency", CALCULATE ( MIN ( 'Table'[Value] ), 'Table'[Indicator] = "Efficiency" ),
        "Weight", CALCULATE ( MIN ( 'Table'[Value] ), 'Table'[Indicator] = "Weight" )
    )
VAR _SUM =
    SUMX ( _Table, [Efficiency] * [Weight] )
VAR _Result =
    DIVIDE ( _SUM, SUMX ( _Table, [Weight] ) )
RETURN
    _Result

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)