topic Re: DAX formula in DAX Commands and Tips

DAX formula

Anonymous — Wed, 29 Sep 2021 16:38:36 GMT

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

Re: DAX formula

amitchandak — Wed, 29 Sep 2021 17:09:41 GMT

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]

))

Re: DAX formula

Anonymous — Wed, 29 Sep 2021 20:11:39 GMT

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.

Re: DAX formula

Samarth_18 — Thu, 30 Sep 2021 04:36:16 GMT

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

Re: DAX formula

Anonymous — Thu, 30 Sep 2021 09:55:37 GMT

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!

Re: DAX formula

Samarth_18 — Thu, 30 Sep 2021 10:48:15 GMT

Anonymous Could you please elaborate a bit with example? Do you need below code to be added in provided solution or you need to calculate both in a same measure?

Non-Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[On Hand Value]),'Inv. Opportunities'[InventoryPlanning]="NB")

Re: DAX formula

Anonymous — Thu, 30 Sep 2021 10:55:18 GMT

Samarth_18 , I would like to add exchange rates like the previous measure. So I would like know what could be the solution for the formula above.

Non-Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[On Hand Value])/9.1,'Inv. Opportunities'[InventoryPlanning]="NB")

In the above formula, I would like to divide the on Hand value with repected exchange rates, and I would not want to apply that for the InventoryPlanning which has NB.