SUM with 2 x IF

[Daryn]

Hi,

I have a table with Dimension for customer & currency. I then have measures summing values (one for UK spend, one for GmbH spend).

We have a basic preset internal transaction exchange rate (say 1.2 for now).

I can convert the GBP using-

Sum ({<WAERK = {'GBP'}>}[ZDE_UMSATZ.NETWR]) + Sum ({<WAERK = {'GBP'}>}NETWR) * 1.2

(blacked out names).

I then tried to turn the expression into a SUM IF. So that the last column 'Totals in €' will include the € currency values as they are ) no multiplication or such.

At some point I will look to include another IF for the $ currency values, but that hasn't got to be now!

WAERK = currency

ZDE_UMSATZ.NETWR = GmbH values

NETWR =  UK values

My currencies are as in the table 'GBP', 'EUR', 'USD'

I thought the below would be correct, but it isn't.

=SUM (IF (WAERK -= {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]*1.2), IF (WAERK = {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]))

I hope that makes sense! Thanks as always for any advice or solution. 

Happy Friday!

Regards, Daryn.

[brunobertels]

Hi 

may be this 

=SUM (IF (WAERK -= {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]*1.2), IF (WAERK = {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]))

sum( if( WAERK<>'EUR', 

[ZDE_UMSATZ.NETWR]+[NETWR]*1.2),

if(WAERK='EUR' ,

ZDE_UMSATZ.NETWR]+[NETWR]))

[Daryn]

Hi Bruno,

Thanks for responding 👍

Unfortunately that gives ;

[brunobertels]

Hoi 

Sorry my bad 

so may be this 

IF(WAERK='EUR',SUM([ZDE_UMSATZ.NETWR]) + SUM([NETWR]),IF(WAERK<>'EUR',

SUM([ZDE_UMSATZ.NETWR])+SUM(NETWR]*1.2) ) 

[Daryn]

Hi and again, thank you.

Unfortunately still an error (I can't see where though).

[brunobertels]

IF(WAERK='EUR',SUM([ZDE_UMSATZ.NETWR]) + SUM([NETWR]),IF(WAERK<>'EUR',

SUM([ZDE_UMSATZ.NETWR])+SUM(NETWR]*1.2) )  ) 

[Daryn]

Hi Bruno,

Again still an error. I can't see why though!

However following your guidance, I have it working with a nested IF:

IF (WAERK='EUR',SUM([ZDE_UMSATZ.NETWR]) + SUM([NETWR]),
IF (WAERK='GBP',SUM([ZDE_UMSATZ.NETWR]*1.2) + SUM([NETWR]*1.2)
)
)

I can add another If for the USD.

Really appreciate your replies and guidance 👍, thank you.  Have a great weekend.