IF statement with SUM

[elkjoperik]

Hi,

I have a challenge that I hope someone here can help me with.

Setup:

Chains are placed in local currency (LOC) and World wide (all chains into one) is set in USD (two different columns)

I want to create a meassure into a table, where I list all the chains, and also include world wide.

Since the figures are in different curriencies, a SUM-calculation on the LOC goes fine but it will state "Infinity" (since  the values is in a different coulmn) on the "World wide".

I want write a meassure that calculates  LOC AND USD as well so I present the results into ONE table.

-->IF SALESCHANNEL = chain, then SUM-calculation1, else; SUM-calulate world wide.  

I've tried with IF(CALCULATE,  IF(VALUES and such, but I don't get it right.   

PS, the SALESCHANNEL (chain / world wide ) is a text column, while to other columns is in whole number

Any tips? 

[v-shex-msft · Accepted solution]

Hi @elkjoperik,

According to your description, you want to transfer “LOC” currency to “worldwide” currency when “SALESCHANNEL” is chain, right?

You can follow the sample below:

I create two test tables.

Currency:
 

Sales:

 
Notice: type is the currency type.

Write the measure to calculate the current sales price.

Sales USD =
var channel=LASTNONBLANK(Sales[SALESCHANNEL],Sales[SALESCHANNEL])
var currentAmount=MAX(Sales[Amount])
var USDRate= LOOKUPVALUE('Currency'[World Wide],'Currency'[ID],MAX(Sales[Type]))
return
if(channel="chain", currentAmount / 100 * USDRate, currentAmount)

Add a table visual to display the result.
 

If above is not help, please provide more detail info about your requirement.

Regards,
Xiaoxin Sheng

[elkjoperik]

Awesome, thanks!

[CahabaData]

It is difficult to understand your data model from your post.  I would suggest you provide a very brief sample of the starting data table, and then a very brief sample of your goal/resulting data table.  If these are table visuals already with measures - a picture of those if possible.