Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Nested IF, DIVIDE and LOOKUPVALUE in a Measure

Hi All,   I'm tyring to put the following calculated column formula into a measure below. Its basically sayIng that if the order is GBP then keep it as it is otherwise, divide the value of the Orde...
Need Help
Show and Tell
  • Syndicate_Admin's avatar
    Syndicate_Admin
    5 years ago

    There @v-janeyg-msft

    Thanks for getting in touch.

    I can't share the data set as there's sensitive data relating to our customers there. I need the measure to do the following, I've stepped this out so its easier to follow:

    1. using an IF statement, if 'Order Details'[Currency] ="GBP" then take that the value in the 'Order Details' [OrderItemCostExTax] column
    2. otherwise divide 'Order Details' [OrderItemCostExTax] with the value in step 3
    3. apply a lookup to get the 'Exchange Rates' [Units per GBP], with a match on dates and currency within the 'Exchange Rates'[Date], 'Order Details' [ReceievedDate],'Exchange Rates'[Currency Code], 'Order Details' [Currency] tables.

    This measure Total Net Sales £ will then be the bases for some of the existing measures I have which are using the calculated table instead:


    Total Cancelled Orders (£) =
    var _Cancelled=CALCULATE(Sum('Order Cancellations'[Total Net Sales £]),FILTER('Order Cancellations','Order Cancellations'[Inc. or Exc. Sales]="Yes"))
    return
    IF(ISBLANK(_Cancelled),0,_Cancelled)

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, Anonymous 

 

It’s my pleasure to answer for you.

According to your description,I think you may be a little confused about measure and column. The calculated column exists in the table, no context is required, and it is a fixed value after loading, but the value of the measure will change according to different contexts, so it needs to be written according to requirements.

Could you share your sample data and your desired result or provide more information about what you want to do with this measure? So we can help you soon.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    There @v-janeyg-msft

    Thanks for getting in touch.

    I can't share the data set as there's sensitive data relating to our customers there. I need the measure to do the following, I've stepped this out so its easier to follow:

    1. using an IF statement, if 'Order Details'[Currency] ="GBP" then take that the value in the 'Order Details' [OrderItemCostExTax] column
    2. otherwise divide 'Order Details' [OrderItemCostExTax] with the value in step 3
    3. apply a lookup to get the 'Exchange Rates' [Units per GBP], with a match on dates and currency within the 'Exchange Rates'[Date], 'Order Details' [ReceievedDate],'Exchange Rates'[Currency Code], 'Order Details' [Currency] tables.

    This measure Total Net Sales £ will then be the bases for some of the existing measures I have which are using the calculated table instead:


    Total Cancelled Orders (£) =
    var _Cancelled=CALCULATE(Sum('Order Cancellations'[Total Net Sales £]),FILTER('Order Cancellations','Order Cancellations'[Inc. or Exc. Sales]="Yes"))
    return
    IF(ISBLANK(_Cancelled),0,_Cancelled)

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      5 years ago

      Hi, Anonymous 

       

      I am glad you can solve the problem by yourself, you are nice. You can mark your answer as the soultion.

      If you have other questions, please feel free to ask me.

       

      Best Regards

      Janey Guo

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.