Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX formula

Hi all,
 
I have used the formula below to calculate the excess buffered stock.
 
Excess Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[Excess Stock Value]))
 
There are 3 regions NA, EMEA and APAC, and the above formula is for NA.
For EMEA and APAC, i need to apply exchange rates. For eg, "Excess Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[Excess Stock Value])) / 9.1" for EMEA.
 
How can I apply the formula such that I can include exchange rates for both EMEA and APAC in the above formula a
 
Any help would be appreciated!
 
Thank you!
Megha
Can I please know how to apply exhange rate 
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Hoping region part of the same table

     

    CALCULATE(SUMX('Inv. Opportunities'

    Switch( True() ,

    [Region] ="EMEA" ,[Excess Stock Value]/9.1,

    [Region] ="APAC" ,[Excess Stock Value]/9.1, // change as per need

    [Excess Stock Value]

    ))

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Hoping region part of the same table

     

    CALCULATE(SUMX('Inv. Opportunities'

    Switch( True() ,

    [Region] ="EMEA" ,[Excess Stock Value]/9.1,

    [Region] ="APAC" ,[Excess Stock Value]/9.1, // change as per need

    [Excess Stock Value]

    ))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you amitchandak!

     

    I applied the formula, but I got the following error. 

     

    Excess Buffered Stock = CALCULATE(SUMX('Inv. Opportunities'Switch( True(),
    Location[Region] ="EMEA" ,'Inv. Opportunities'[Excess Stock Value]/9.1,
    Location[Region] ="APAC" ,'Inv. Opportunities'[Excess Stock Value]/9.1,
    'Inv. Opportunities'[Excess Stock Value]
    ))

     

    The syntax for 'Switch' is incorrect. (DAX(CALCULATE(SUMX('Inv. Opportunities'Switch( True(),Location[Region] ="EMEA" ,'Inv. Opportunities'[Excess Stock Value]/9.1,Location[Region] ="APAC" ,'Inv. Opportunities'[Excess Stock Value]/9.1,'Inv. Opportunities'[Excess Stock Value])))).

     

    I tried the applying what is told in the error, it still shows error.

    • Samarth_18's avatar
      Samarth_18
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi Anonymous ,

       

      You have missed some comma's and closing brackets in your formula. Below code would be ideal code which Amit suggested:-

      Excess Buffered Stock =
CALCULATE (
    SUMX (
        'Inv. Opportunities',
        SWITCH (
            TRUE (),
            Location[Region] = "EMEA", 'Inv. Opportunities'[Excess Stock Value] / 9.1,
            Location[Region] = "APAC", 'Inv. Opportunities'[Excess Stock Value] / 9.1,
            'Inv. Opportunities'[Excess Stock Value]
        )
    )
)

       

      Thank you,

      Samarth

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks a lot! Samarth_18 !

         

        Can I also know how to apply the same if there is a condition?

         

        For eg:

        Non-Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[On Hand Value]),'Inv. Opportunities'[InventoryPlanning]="NB")
         
        How to apply the same for the formula above?
         
        Thank you for your help!