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 of some KPIs, but all the data is in one column.

 

And this measure should return me 83.33, from (90 * 5 + 80 * 10) / (5 + 10).

How can I use DAX to get this result (Without unpivot)?

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

     

16 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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 )
    • Oleg222's avatar
      Oleg222
      Helper II
      5 years ago

      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?

      • Fowmy's avatar
        Fowmy
        Super User
        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)

  • Oleg222's avatar
    Oleg222
    Helper II
    5 years ago

    Sorry friends, one important think (I forgot, my fault) - in column "Line" should stand date.

    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      Oleg222 

      Yes, it should work for dates as well with many date periods as well.