Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Niels_T
Post Patron
Post Patron

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:

 

Niels_T_0-1645522754036.png

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

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.

MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.