'Basket Analysis' pattern extension - Unique customers to a store

[sebastian_ebg]

Looking for some advice on next steps for extending the excellent Basket Analysis pattern described by Marco and Alberto.

I'm trying to re-work this model to look at customer spread across different stores by filtering a target store(s). I can get it to return the sales value that customers who have shopped at the target store have spent across all stores over a time period using this measure:

ValueAtOtherStores = CALCULATE(SUM(Trans[ValueEx]),CALCULATETABLE(SUMMARIZE(Trans,Trans[CustomerKey]),ALL(Trans),USERELATIONSHIP(Trans[StoreNumber],Stores[StoreNumber])))

The model is a little more simplified than Marco and Alberto's because I don't care about the products, just the customer and their shopping habits. The only complication here is that the data is aggregated by customer, store, year and month - so per store, there's potentially multiple entries per customer.

The next step I'm trying to achieve is a measure to calculate the number of customers who are unique to that store (have only shopped there, no where else) and can't get the logic to work. The plain-spoken logic should be "Get a distinct count of customers where the distinct count of the stores that they've shopped at is one when using the relationship".

Extending the [Customers with No Filter Products Classic] measure in their article, I've got the following:

Sole Store Shopper = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(Trans,USERELATIONSHIP(Trans[StoreNumber],'Store Filter'[StoreNumber])),Trans[CustomerKey],"NumStores",DISTINCTCOUNT(Trans[StoreNumber])),[NumStores]=1))

I believe that the problem with this is that it's not a distinct count of the customer keys that match the filter, instead it's a count of the rows where those customers have transacted at that store (may have been multiple times per customer).

Can anyone provide any guidance on how to achieve the desired result?

[v-yuezhe-msft]

@sebastian_ebg,

Could you please share sample data of your tables and post expected result here? You can follow the guide in this post to share sample data.



Regards,

Lydia

[sebastian_ebg]

Sure thing! Here's some sample data.

CustomerKey	StoreNumber	Year	Month	Qty	ValueEx	NumTrans
351392	101	2018	8	5	43	1
351392	203	2018	7	1	167	1
632044	101	2018	5	3	50	3
632044	460	2017	10	4	82	2
632044	460	2018	6	4	161	1
975858	101	2017	4	5	41	2
975858	101	2018	6	1	154	1

In the example above, the [Sole Store Shopper] measure should return a value of 1 when filtered to store 101. This is because there's only one customer that has shopped exclusively at store 101 (CustomerKey = 975858). Every other customer has shopped somewhere other than 101.

Extending this, I should be able to then create a measure to identify the amount of money that those customers who have only shopped at that store have spent there. In this instance, that measure would return $195 ($41 + $154 for the customer 975858).

Effectively, we're trying to use this tool to identify the potential unique customers and revenue we'd lose if we closed or moved a store.

[Gazzer]

Hi,

As always, I have to avoid the DAX solution (because I don't know DAX!), so I have done something really quickly with Power BI's built-in functions:

All I did was group by CustomerKey and StoreNumber to get the unique Customer/Store combinations. Then I grouped again by the CustomerKey and filtered for Count = 1. Lastly, I merged with the original data to get the rest of the columns.

[sebastian_ebg]

That's certainly the desired result @Gazzer, but need to achieve it in DAX because the filters of both target store and time-period will need to be applied dynamically.

Any direction @v-yuezhe-msft?

[Gazzer]

@sebastian_ebg I may have missed something, but I don't see how this won't be dynamic - could you elaborate a little please?

I have created an example file with filters, to see if this is what you mean...

Sole Shopper Power BI Example

[sebastian_ebg]

Thanks for going to the effort of making an example. Definitely helped me understand your working better. There's a few things that wouldn't work in your solution however and I'll try and explain them as best I can here.

• The source data actually has almost a billion rows. Query Editor will struggle to perform manipulations on that much data because (I believe) it's not part of the optimised analysis cube model until after the Query Editor step.
• In your solution, tables are only calculated when data is refreshed therefore they aren't dynamic. This affects:
  • Time Slicing - As well as slicing by a target store, I will also be slicing by time period. (E.g. find me [Sole Store Shoppers] at store 101 between 2017-08 and 2018-02)
  • Multi-selection - Selecting multiple target stores should find customers who are unique to either store. 

Appreciate the unique solution to the one specific problem, but it doesn't mesh well with the rest of my solution due to the issues mentioned above.

There's definitely going to be a way to achieve the result with a DAX expression, I've just hit the limits of my understanding on how to achieve it.

[Gazzer]

Billions of rows - yes, that makes much more sense why my simple concept won't work for you!

Thanks for taking the trouble to explain - hopefully that will help someone else come up with a solution for you.