Calculation gets bad performance when I add -1

[LotteLaugesen]

Hi

I have this calculation

CALCULATE(
DIVIDE(SUM(FT_ManufacturingOrderHeader[Manufactured Quantity KG]), SUM(FT_ManufacturingOrderHeader[Kg Fillets]))) 

that performs fine (<1 sek), but when I add -1 to it 

CALCULATE(
DIVIDE(SUM(FT_ManufacturingOrderHeader[Manufactured Quantity KG]), SUM(FT_ManufacturingOrderHeader[Kg Fillets]))) - 1

then it takes > 40 sek no matter if I put -1 inside or outside the parentheses.

Can anybody help?

[OwenAuger]

Hi @LotteLaugesen 

I'm assuming this is a measure.

You do have do watch out when adding a constant to a measure, as it means a value is returned for any combination of dimensions that might be included in a visual, potentially impacting performance.

Also, the outer CALCULATE is not needed if this is a measure.

I would try this:

VAR Ratio =
    DIVIDE (
        SUM ( FT_ManufacturingOrderHeader[Manufactured Quantity KG] ),
        SUM ( FT_ManufacturingOrderHeader[Kg Fillets] )
    )
RETURN
    IF ( NOT ISBLANK ( Ratio ), Ratio - 1 )

Alternatively, you could write this (should be equivalent):

VAR ManQtyKG =
    SUM ( FT_ManufacturingOrderHeader[Manufactured Quantity KG] )
VAR KGFillets =
    SUM ( FT_ManufacturingOrderHeader[Kg Fillets] )
RETURN
    DIVIDE ( ManQtyKG - KGFillets, KGFillets )

 Regards,

Owen

[LotteLaugesen · Accepted solution]

Thank you, Owen, it works perfect and quick 😀