Tuesday, August 07, 2012 8:05 AM
We effectively have two types of transactions, 'customer orders' and 'cash and carry'. To state the fairly obvious, 'Customer Orders' are those placed by the customer in advance and collected later and 'cash and carry' is where the customer walks into the store and purchases an in stock item on the spot. We recognise the 'Customer Order' sale when the customer collects the item and pays the remaining balance.
My aim is to analyse where in the process of Finalising a Customer Order, the customer also makes additional purchases from the store that would be processed as a totally separate transaction.
I either need a calculated column that flags the 'Cash&Carry' records where that customer has also conducted a 'Customer Order' transaction on the same day or a measure that will sum the sales under the same conditions.
Any help appreciated.
Tuesday, August 07, 2012 12:16 PM
The following formula will create the calculated column:
=IF([Transaction Type]="Cash&Carry" &&
Transactions[Transaction Type]="Customer Order") > 0;
Wednesday, August 08, 2012 8:18 AM
Frederik, this works like a charm! Thank You.
Can you explain how this bit works: ALLEXCEPT(Transactions;Transactions[Customer Reference];Transactions[Date])
I understand the rest its just its not intuitive to me - I guess it has to create a table that includes everything except where Customer Reference and Date are the same as the current row!
Wednesday, August 08, 2012 12:34 PM
CALCULATE() turns the row context into a filter context. With the 2nd, 3rd, ... argument we can create a new filter context.
means ignore all filters but keep those on Customer Reference and Date, or other words look for rows on the same date with the same customer reference
The 3rd argument of CALCULATE (Transactions[Transaction Type]="Customer Order") means: only look for Customer Orders.
If we find more than 1 row return Yes, otherwise return No.
Wednesday, August 08, 2012 11:30 PMPowerful stuff! Thanks again.