PowerPivot Cohort Analysis

0
votes

I'm trying to do cohort analysis using Excel's PowerPivot. I have a table recording which users have purchased which products in which months eg.

UserID Product Date Quantity
1 Ham Mar 15 2
1 Cheese Jan 15 7
2 Ham Mar 15 8
3 Fish Mar 15 2
2 Cheese Apr 15 8

I want to use a calculated field to filter for a cohort of users who purchased a given product in a given month but be able to analyse all their purchases.

Eg cohort Ham, March 15

--> Users 1, 2

UserID Product Date Quantity
1 Ham Mar 15 2
1 Cheese Jan 15 7
2 Ham Mar 15 8
2 Cheese Apr 15 8

I know this could be done easily using SQL but I am working with colleagues who prefer to use Excel over Access/Some SQL interface.

Thankyou

1
excelpowerpivotdax

1 Answers

1
votes

Create a calculated column like this:

=if([UserID]&SlicerValue=[UserID]&[Product],[UserID])

where HAM would be selected from slicer created from a table of unique products.