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 Non-buffered stock.
 
Non-Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[On Hand Value]),'Inv. Opportunities'[InventoryPlanning]="NB")
 
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, "Non-Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/9.1,'Inv. Opportunities'[InventoryPlanning]="NB")" 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
 
Help required
  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    4 years ago

    Hi Anonymous ,

    Use this:

    Measure= IF(SELECTEDVALUE(Table[Region])= "EMEA", CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/9.1,'Inv. Opportunities'[InventoryPlanning]="NB"), IF(SELECTEDVALUE(Table[Region])= "APAC)" CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/6.7,'Inv. Opportunities'[InventoryPlanning]="NB"),  CALCULATE(SUM('Inv. Opportunities'[On Hand Value]),'Inv. Opportunities'[InventoryPlanning]="NB"))

    Mark this as a solution, if i answered your question.

    Thanks

     

5 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Hi Anonymous ,

     

    Use the below Measure:

    Measure= IF(AND(SELECTEDVALUE(Table[Region])= "EMEA", SELECTEDVALUE(Table[Region])= "APAC)", CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/9.1,'Inv. Opportunities'[InventoryPlanning]="NB"), CALCULATE(SUM('Inv. Opportunities'[On Hand Value]),'Inv. Opportunities'[InventoryPlanning]="NB"))


    Mark this as asolution, if I answered your question. Kudos are always appreciated.

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you! Tanushree_Kapse .

      Both the regions have two different exchange rates.

      For eg: EMEA has 9.1 and APAC has 6.7

       

      How can I implement that?

       

      Thank you!

      • Tanushree_Kapse's avatar
        Tanushree_Kapse
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        Hi Anonymous ,

        Use this:

        Measure= IF(SELECTEDVALUE(Table[Region])= "EMEA", CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/9.1,'Inv. Opportunities'[InventoryPlanning]="NB"), IF(SELECTEDVALUE(Table[Region])= "APAC)" CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/6.7,'Inv. Opportunities'[InventoryPlanning]="NB"),  CALCULATE(SUM('Inv. Opportunities'[On Hand Value]),'Inv. Opportunities'[InventoryPlanning]="NB"))

        Mark this as a solution, if i answered your question.

        Thanks

         

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

    Anonymous  how are you storing your excahnge rate for EMEA, APAC, NA...are you storing in a seperate table?