Measure in totals

[Oleg222]

Please help with the measure - there is a flat table according to which the indicator is calculated:

Weighted Average3 =

DIVIDE (

SUMX (

VALUES ( Table3[Date] ),

CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Efficiency" )

* CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Weight" )

),

SUMX (

VALUES ( Table3[Date] ),

IF ( CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Efficiency" ), 1 )

* CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Weight" )

)

)

If you count separately for the branch, then everything is fine, but in the total amount the value is incorrect.
Please tell me:
- how effective is this measure (the denominator needs the filter "Efficiency" <> 0, so I used IF ( CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Efficiency" ), 1 ))
- how to fix the total indicator.

https://drive.google.com/file/d/1s1mlFoqKb-eLB0wKqvSL_XcVz6y0eMKd/view?usp=sharing

[Oleg222]

@v-kkf-msft The problem was not solved. Unfortunately, I don't know how to explain the logic of the formula. There are daily data on the indicator (moisture) and product weight. A formula for calculating the weighted average is required - for calculating by periods (weeks, months, years).

[v-kkf-msft]

Hi @Oleg222 ,

I'm not sure if my understanding is correct. I think column "Efficiency" is the variable and column "Weight" is the corresponding weight.  Does the total part you said it is incorrect mean the part shown in the red circle in the figure below? The calculation formula of the total part I understand is 

           (0.07*636.5+0.07*626.74+0.03*602.9+0.02*621.6)/(636.5+626.74+602.9+621.6) = 0.048

Where does it have a problem?

Best Regards,
Winniz

[Oleg222]

@v-kkf-msft  The problem in All, its incorrect (I think the problem is in the numerator - in the total, the sum "Efficiency" is multiplied with the sum of the denominator, but it should be multiplied every day (weighted average)).

[v-kkf-msft · Accepted solution]

Hi @Oleg222 ,

Try the following formula:

Measure = 
DIVIDE(
    SUMX(
        SUMMARIZE( Table3, Table3[Date], Table3[Branch] ),
        [Efficiency] * [Weight]
    ),
    SUMX(
        VALUES(Table3[Date]),
        IF ( CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Efficiency" ), 1 )
            * CALCULATE ( SUM ( Table3[Value] ), Table3[Measures] = "Weight" )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oleg222]

@v-kkf-msft  Thank you. It seems to work.

[v-kkf-msft]

Hi @Oleg222 ,

Does your problem have been solved? 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Oleg222]

@aj1973  "Efficiency" in different periods, weighted by "Weight". The result by periods and by branches is OK, but the total amount is not correct. 

[v-kkf-msft]

Hi @Oleg222 ,

I don't understand your formula. It seems to me that the calcalation result of measure "Weighted Average3" is the same as the result of measure "Efficiency".

Could you please give me an example of what total value you expect?

Best Regards,
Winniz

[Oleg222]

I think the problem is in the numerator - in the total, the sum "Efficiency" is multiplied with the sum of the denominator, but it should be multiplied every day (weighted average).

[aj1973]

Hi @Oleg222 

Your formula is not clear! Avarage of what that you want to see? you want to DIVIDE what by what?