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 excess buffered stock.   Excess Buffered Stock = CALCULATE(SUM('Inv. Opportunities'[Excess Stock Value]))   There are 3 regions NA, EME...
  • amitchandak's avatar
    amitchandak
    4 years ago

    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]

    ))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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!
    • Samarth_18's avatar
      Samarth_18
      Icon for Community Champion rankCommunity Champion
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.