Percentage of People Cross Shopping Different Locations

[matthewjudd]

Good morning everyone,

This should be a relatively simple solution, but I am still early in my Power BI journey.

I have a list of locations, and a list of people who visited each location. Some people may appear in one location multiple times, or both locations multiple times. Some people will only shop one of the locations, while others will shop both. See below table for an example of what the data looks like:

Location	Person
Walmart	John
Home Depot	John
Walmart	Dave
Home Depot	Dave
Home Depot	Mary
Walmart	Sally
Walmart	Sally
Walmart	Paul
Target	Paul
Walmart	Caroline
Home Depot	Mary
Walmart	Samantha
Walmart	Samantha
Home Depot	Sally
Target	Jim
Target	Alexandra
Walmart	Alexandra

There are two percentages I would like to know:

Simple Cross Shop - After removing duplicate visits by the same person, what percentage of people cross-shop between different retailers?

The first step in this is to remove duplicate trips (Sally went to Walmart twice, Mary and Samantha went to Home Depot twice). Once duplicate visists are removed the list of entries is simplified and is shorter (see below).

Location	Person
Walmart	John
Home Depot	John
Walmart	Dave
Home Depot	Dave
Home Depot	Mary
Walmart	Paul
Target	Paul
Walmart	Caroline
Walmart	Samantha
Walmart	Sally
Home Depot	Sally
Target	Jim
Target	Alexandra
Walmart	Alexandra

Based on the above list I would need a Power BI calclation that would be able to identify that John, Dave and Sally all shopped bith Walmart and Home Depot. While Alexandra was the only one to shop both Target and Walmart (see below summary table).

	Home Depot	Walmart	Target
Home Depot	--	John, Dave, Sally	(N/A)
Walmart	--	--	Alexandra
Target	--	--	--

The final output would be something along these lines:

Count of people who shopped both Home Depot & Walmart /divided by/ total people that shopped Home Depot -or- Walmart

Count of people who shopped both Home Depot & Target /divided by/ total people that shopped Home Depot -or- Target

Count of people who shopped both Walmart & Target /divided by/ total people that shopped Home Depot -or- Target

	Home Depot	Walmart	Target
Home Depot	--	3/11 (27.3%)	0/7 (0.0%)
Walmart	--	--	1/10 (10%)
Target	--	--	--

Let me know if you have any ideas on how best to tackle this data problem!

Thanks you very much!!

[v-luwang-msft]

Hi @matthewjudd ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[moizsherwani · Accepted solution]

@matthewjudd quite a challenging one and there are a number of way to solve this including

1) self merging the table to see what combinations exist

2) create disconnected tables for the rows and columns of the matrix along with a list of person to then see what combinations exists (I have chosen this way)

I have attached the PowerBI file for this I hope this gets you on your way.

Btw in your example you stated Alexandra as being the only one who shopped at Walmart and Target even though the data shows Paul as being in the same cohort.

[moizsherwani]

Hi @matthewjudd, can you please give an example of what the expected output would be (with real calculations) for the sample data you have provided (this is in line with "best practices when asking questions guidelines on the PowerBI community forum 🙂")?

[matthewjudd]

Thanks for the recommendation Moiz! Much appreciated. I have revised my initail post providing more detials on what the planned output is, and I how to get there. Let me know if you need anything else clarified!