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)

 

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? 

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

3 Replies

  • 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

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    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.