Nested IF, DIVIDE and LOOKUPVALUE in a Measure

[Anonymous]

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 OrderItemCostIncTax with the value that comes back from the LOOKUP against the Exchange Rates table. 

Total Gross Sales £ = IF('Order Details'[Currency]="GBP",'Order Details'[OrderItemCostIncTax],

DIVIDE('Order Details'[OrderItemCostIncTax],

LOOKUPVALUE('Exchange Rates'[Units per GBP],'Exchange Rates'[Date], 'Order Details'[ReceievedDate], 'Exchange Rates'[Currency Code], 'Order Details'[Currency])))

Can anyone please help me get this into a measure?

[v-janeyg-msft]

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 · Accepted solution]

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]

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.

[P_D_G]

Hi @Anonymous ,

you need to add some aggregation reference (min, max, avg, sum, etc) to the column otherwise you cannot refernce the values in a Measure.

try somethin like this:

if(
    min('Order Details'[Currency]) = "GBP",
        min('Order Details'[OrderItemCostIncTax'),
        etc)

[amitchandak]

@Anonymous , what is the error you are getting.

Refer how can you copy values from one table to another -https://www.youtube.com/watch?v=czNHt7UXIe8 (lookupvalue, related and other)

[Anonymous]

Hi @amitchandak 

When I copied and pasted the formula into a Measure it came up with the below error messsage:

"A single value for column 'OrderItemCostIncTax' in table 'Order Details' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

The issue with this is this, currency conversions are done on a daily basis therefore in my Excghange Rates table I have the rates for each date of the month and I believe applying an aggregate like Min or Max will not bring back the exact currency conversion for that order on the specified date. Hence the reason why I created a calculated column as a work around, but this is now causing issues with me trying to change the Date and Order Details table link to Both in the Cross Filter section (this links to another post SAMEPERIODLASTYEAR which you have helped me on) where I'm getting the following error:

" A circular dependency was detected: Order Details[Total Gross Sales £], Order Details[Gross Profit Margin], Order Details[Total Gross Sales £]."

Having looked into this, it's because I need to change the calculated columns listed in the above error message to Measures. Order Details[Total Gross Sales £] and Order Details[Total Net Sales £] are the only two calculated columns that I need to convert to Measures within the Order Details table as the other two columns have been converted.

Any help and guidance here is greatly welcome!

[amitchandak]

@Anonymous , I think you formula should work like a new column in Order Details.

To make it work as measure you need use summarize or values to force a row level context

A mesure like

Total Gross Sales £ = sumx( summarize('Order Details','Order Details'[Currency], 'Order Details'[ReceievedDate], "_1", calculate( IF(max('Order Details'[Currency])="GBP",'sum(Order Details'[OrderItemCostIncTax]),
DIVIDE(sum('Order Details'[OrderItemCostIncTax]),
LOOKUPVALUE(Average('Exchange Rates'[Units per GBP]),max('Exchange Rates'[Date]), max('Order Details'[ReceievedDate]), max('Exchange Rates'[Currency Code]), max('Order Details'[Currency]))))), [_1])

[Anonymous]

Hi @amitchandak 

Just a quick question what does the "_1" and the [_1] do in the formula you have below?

[Anonymous]

Hi @amitchandak 

Thanks for this, I typed it in and got this error message:

"Too many arguments were passed to the AVERAGE function. The maximum argument count for the function is 1."

Below was what I typed in:

Total Gross Sales £ = SUMX( SUMMARIZE('Order Details','Order Details'[Currency], 'Order Details'[ReceievedDate], "_1", CALCULATE(IF(MAX('Order Details'[Currency])="GBP",SUM('Order Details'[OrderItemCostIncTax]),

DIVIDE(SUM('Order Details'[OrderItemCostIncTax]),
LOOKUPVALUE(AVERAGE('Exchange Rates'[Units per GBP],MAX('Exchange Rates'[Date]),MAX('Order Details'[ReceievedDate]), MAX('Exchange Rates'[Currency Code]),MAX('Order Details'[Currency]))))),[_1])