cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

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
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.

2 REPLIES 2
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.

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.

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.