Basket Analysis in Powerpivot /Dax

[barnettjacob]

I'm trying to work out how to do a 'Basket Analysis' in Powerpivot using DAX. I've read Alberto's excellent piece and although I'm looking for a similar approach, the fundamental difference is that I'm looking at establishing patterns at a transaction level not a customer level as Alberto does.

Using the 'unattached' Slicer method that Alberto employs in his piece and that Rob uses extensively I want to be able to select a product code from the list and return the sales of other products that were in transactions that featured the slected product.

Here is a fragment of the type of sales data I'm looking at:

Trans Ref Product Units
11100057 Product A 1
11100057 Product B 2
11100057 Product C 1
11100058 Product A 1
11100058 Product B 1
11100059 Product B 1
11100059 Product D 2

On the one hand I've got a reasonable idea of how this should work but just can't quite it to come together! I see the steps as something like:

1 - I select Product A from the slicer.
2 - The measure establishes the Trans Refs that are applicable (in this case 11100057 and 11100058).
3 - The measure then returns a filtered version of the sales table that only has the applicable transactions in.
4 - The measure filters out sales of Product A from the filtered sales table.
5 - I can then happlily analyse the result using all my usual dimensions!

Hope this makes sense!

Rob, Marco, Alberto, I guess I'm looking in your direction on this but would welcome input from anybody!

Regards
Jacob

 

[powerpivotpro]

Hi Jacob. I've been watching this one but I don't think I am the guy to address it. I'll check and see if Marco/Alberto have seen your question. I know they are very busy right now with their books though.

 

[barnettjacob]

Thanks Rob, appreciate it. Happy to provide a sample file if that's going to help.

Hope this kind of basket analysis is possible - its notoriously difficult to do with 'standard' tools given the amount of data that's generally involved!

 

[barnettjacob]

For anybody who is interested, Alberto solved my problem:

=CALCULATE (
SUM ('TY Sales'[Sales EX Gst]),
FILTER (
VALUES ('TY Sales'[Transaction Ref]),
CALCULATE (
COUNTROWS ('TY Sales'),
USERELATIONSHIP ('TY Sales'[productcode], 'Basket Analysis SKUs'[Filter Product Code])
) > 0
&&
CALCULATE (
COUNTROWS ('TY Sales'),
USERELATIONSHIP ('TY Sales'[productcode], 'Product'[Product Code])
) > 0
),
USERELATIONSHIP ('TY Sales'[productcode], 'Product'[Product Code])
)


Where 'TY Sales' is the Fact table which has NO relationship to the 'Product' Dimension table. 'Basket Analysis SKUs'[Filter Product Code] is an entirely separate unlinked set of productcodes used to populate the slicer.

Thanks Alberto!!!!!!