DAX formula

[Anonymous]

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

[smpa01]

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

[Tanushree_Kapse]

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]

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 · Accepted solution]

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

[Anonymous]

Thanks a lot! @Tanushree_Kapse