none
Basket Analysis in Powerpivot /Dax

    Question

  • 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:



    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!
    Thanks
    Jacob


    *************'cross post from Mr Excel'********
    Tuesday, February 14, 2012 9:23 AM

Answers

  • Barnett, it would be much more useful if you can share the workbook, with some data inside. It is hard to understand your data model just by looking at a fact table.

    The general idea, looking at your data, should be the one I highlighted in the post but, depending on your specific data model, it might need some modifications.


    Alberto Ferrari
    http://www.powerpivotworkshop.com

    • Marked as answer by barnettjacob Monday, February 20, 2012 9:16 PM
    Thursday, February 16, 2012 5:24 PM

All replies

  • Hi barnettjacob,

    What's your real question? do you have some uncomprehension to Alberto's solution or want to know why Alberto's solution works?


    Challen Fu

    TechNet Community Support

    Thursday, February 16, 2012 6:32 AM
    Moderator
  • Barnett, it would be much more useful if you can share the workbook, with some data inside. It is hard to understand your data model just by looking at a fact table.

    The general idea, looking at your data, should be the one I highlighted in the post but, depending on your specific data model, it might need some modifications.


    Alberto Ferrari
    http://www.powerpivotworkshop.com

    • Marked as answer by barnettjacob Monday, February 20, 2012 9:16 PM
    Thursday, February 16, 2012 5:24 PM
  • Thanks Alberto, I've emailed you a workbook.

    Regards

    Jacob

    Friday, February 17, 2012 11:26 AM
  • 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 an inactive relationship to the 'Product' Dimension table.  'Basket Analysis SKUs'[Filter Product Code] is an entirely separate set of productcodes used to populate the slicer which again has an inactive relationship with the product code in the Fact table.

    Thanks Alberto!!!!!!



    • Marked as answer by barnettjacob Monday, February 20, 2012 9:16 PM
    • Unmarked as answer by barnettjacob Monday, February 20, 2012 9:16 PM
    • Edited by barnettjacob Monday, February 20, 2012 9:44 PM
    Monday, February 20, 2012 9:15 PM
  • Hi barnettjacob,

    Also thanks for sharing the information.


    Challen Fu

    TechNet Community Support

    Tuesday, February 21, 2012 1:25 AM
    Moderator
  • I also blogged about Applied Basket Analysis in Power Pivot using DAX here:

    http://blog.gbrueckl.at/2014/02/applied-basket-analysis-in-power-pivot-using-dax/

    just for reference

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, February 19, 2014 3:05 PM
    Answerer