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

     

Fowmy's avatar
Fowmy
Super User
5 years ago

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

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's avatar
    Fowmy
    Super User
    5 years ago

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

    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