topic Re: DAX formula in DAX Commands and Tips

DAX formula

Anonymous — Wed, 06 Oct 2021 12:21:30 GMT

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

Re: DAX formula

Tanushree_Kapse — Wed, 06 Oct 2021 14:17:10 GMT

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

Re: DAX formula

Anonymous — Tue, 12 Oct 2021 14:16:45 GMT

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!

Re: DAX formula

Tanushree_Kapse — Tue, 12 Oct 2021 14:30:38 GMT

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

Re: DAX formula

smpa01 — Tue, 12 Oct 2021 14:31:27 GMT

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

Re: DAX formula

Anonymous — Tue, 12 Oct 2021 14:58:23 GMT

Thanks a lot! Tanushree_Kapse