Measure from data in one column

[Oleg222]

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

[Oleg222]

@FrankAT @Fowmy  thank for help. Will your formula work if there are many date periods in "Line"?

[Fowmy]

@Oleg222 

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

[Oleg222]

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

[FrankAT]

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)

[Fowmy]

@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]

@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 · Accepted solution]

@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]

@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?

[Fowmy]

@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?

[Oleg222]

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

)

[Fowmy]

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

[Oleg222]

@Fowmy  unfortunately it doesn't work. For example - 09.06.2021 Efficiency1 = 0, Weight1 = 300. We remove all zeros with the filter. When counting, the value Weight1 = 300 is added to the denominator, but it shouldn't.

[Fowmy]

@Oleg222 

What would be the logic to remove the data?
I assumed you always have either both zero or both have values. You can either fix your data or tell me the correct logic to apply. 

Provide sample data with the expected scenario as well.






 

[Oleg222]

@Fowmy  sorry about that, below is the file in which everything is fixed. Considering that for 25.03, 28.03, 31.03 Efficiency is absent - Weight should not be added to the denominator (highlighted in red). In the table there are 3 options for the measure - the first (more complex but with the correct final result), the second (the best, in which we need to add a filter by dates where Efficiency is absent, the total is not correct) and the third (the second formula I corrected). I think my fixes can be done better.

https://drive.google.com/file/d/1QNNOtN4jmSF1E3qtk_w9YvNGPNYRnY0r/view?usp=sharing

[Fowmy]

@Oleg222 

Can you reshare the sample data with this zero scenario with the expected results as well?

[Oleg222]

@Fowmy  https://drive.google.com/file/d/1OEFqlARqQ1c5Xo9Z_-Z4bm5KAcOfCczq/view?usp=sharing