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)

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.