Forum Discussion

Niels_T's avatar
Niels_T
Post Patron
4 years ago
Solved

IF statement won't work properly

I have written a long if statement that goes as followed:

 

Total Direct Unit Cost = 
VAR CurrencyUSD = CALCULATE(SUM('Currency Rate'[Units per EUR]),'Currency Rate'[Currency] = "USD")
RETURN

IF(CALCULATE(SUM('Stock Forecast'[Total Remaining Direct Unit Cost]),
	'Vendor List'[EU / Non-EU] IN { "EU" }), SUM('Stock Forecast'[Total Remaining Direct Unit Cost])*1.03,

        IF (CALCULATE(SUM('Stock Forecast'[Total Remaining Direct Unit Cost]), 'Vendor List'[EU / Non-EU] IN { "non-EU (EUR)" }), SUM('Stock Forecast'[Total Remaining Direct Unit Cost])*1.06,

            IF(CALCULATE(SUM('Stock Forecast'[Total Remaining Direct Unit Cost]),
	        'Vendor List'[EU / Non-EU] IN { "non-EU (USD)" }), DIVIDE(SUM('Stock Forecast'[Total Remaining Direct Unit Cost])*1.35,CurrencyUSD),
            
                IF( ISBLANK(SUM('Stock Forecast'[Total Remaining Direct Unit Cost])) = TRUE, 0 ))))

Basically:

Set a var for currency rate of USD

 

IF 'Vendor List'[EU / Non-EU] contains "EU" then I have to take the sum of [Total Remaining Direct Unit Cost]*1.03

 

ELSE IF 'Vendor List'[EU/Non-EU] contains "non-EU (EUR)" then I have to take the sum of [Total Remaining Direct Unit Cost]*1.06

 

ELSE 'Vendor List'[EU/Non-EU] contains "non-EU (USD)" then I have to take the sum of [Total Remaining Direct Unit Cost]*1.35 / Currency Rate

 

Unfortunately with the code I wrote, I won't get the desired result:

 

1: The first column is the amount without the measure.

2: The second column is the amount with the calculation written above (but calculation is wrong)

3: The third column indicates if the amount is for EU, non-EU (EUR) or non-EU (USD),

4: The fourth column is a measure with the desired result for the column that contains non-EU (EUR)

 

This is the measure of the fourth column:

CALCULATE(
	SUM('Stock Forecast'[Total Remaining Direct Unit Cost]),
	'Vendor List'[EU / Non-EU] IN { "non-EU (EUR)" }
)*1.06

 

How can I make sure that I get the result of the fourth column for non-EU (EUR)?

Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi Niels_T ,

     

    Based on my understanding, try to modify your measure like so:

    Total Direct Unit Cost =
VAR CurrencyUSD =
    CALCULATE (
        SUM ( 'Currency Rate'[Units per EUR] ),
        'Currency Rate'[Currency] = "USD"
    )
RETURN
    IF (
        'Vendor List'[EU / Non-EU] IN { "EU" },
        SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.03,
        IF (
            'Vendor List'[EU / Non-EU] IN { "non-EU (EUR)" },
            SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.06,
            IF (
                'Vendor List'[EU / Non-EU] IN { "non-EU (USD)" },
                DIVIDE (
                    SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.35,
                    CurrencyUSD
                ),
                IF ( ISBLANK ( SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) ), 0 )
            )
        )
    )

     

    Or like so:

    Total Direct Unit Cost =
VAR CurrencyUSD =
    CALCULATE (
        SUM ( 'Currency Rate'[Units per EUR] ),
        'Currency Rate'[Currency] = "USD"
    )
RETURN
    SWITCH (
        TRUE (),
        SWITCH (
            'Vendor List'[EU / Non-EU],
            "EU", SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.03,
            "non-EU (EUR)", SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.06,
            "non-EU (USD)",
                DIVIDE (
                    SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.35,
                    CurrencyUSD
                )
        ), ISBLANK ( SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) ),
        0
    )

     

     

    Best Regards,

    Icey

     

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

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi Niels_T ,

     

    The main issue of your formula is that the IF statement is not comparing anything so the calculation is always true and returns incorrect totals.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Niels_T ,

     

    Based on my understanding, try to modify your measure like so:

    Total Direct Unit Cost =
VAR CurrencyUSD =
    CALCULATE (
        SUM ( 'Currency Rate'[Units per EUR] ),
        'Currency Rate'[Currency] = "USD"
    )
RETURN
    IF (
        'Vendor List'[EU / Non-EU] IN { "EU" },
        SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.03,
        IF (
            'Vendor List'[EU / Non-EU] IN { "non-EU (EUR)" },
            SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.06,
            IF (
                'Vendor List'[EU / Non-EU] IN { "non-EU (USD)" },
                DIVIDE (
                    SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.35,
                    CurrencyUSD
                ),
                IF ( ISBLANK ( SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) ), 0 )
            )
        )
    )

     

    Or like so:

    Total Direct Unit Cost =
VAR CurrencyUSD =
    CALCULATE (
        SUM ( 'Currency Rate'[Units per EUR] ),
        'Currency Rate'[Currency] = "USD"
    )
RETURN
    SWITCH (
        TRUE (),
        SWITCH (
            'Vendor List'[EU / Non-EU],
            "EU", SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.03,
            "non-EU (EUR)", SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.06,
            "non-EU (USD)",
                DIVIDE (
                    SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) * 1.35,
                    CurrencyUSD
                )
        ), ISBLANK ( SUM ( 'Stock Forecast'[Total Remaining Direct Unit Cost] ) ),
        0
    )

     

     

    Best Regards,

    Icey

     

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