Forum Discussion

elkjoperik's avatar
elkjoperik
Helper II
9 years ago
Solved

IF statement with SUM

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)   ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    elkjoperik
    Helper II
    9 years ago

    Awesome, thanks!