Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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]

))

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous
Not applicable

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!

@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")

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous
Not applicable

@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.

 

amitchandak
Super User
Super User

@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]

))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors