Calculating cross-tabulation in unpivoted table

[astotz]

I have a table of the form: [id, program, enrollment_date] where id is the unique ID of a customer, program is 1 of 12 programs that customer has enrolled in, and enrollment_date is the date that they enrolled. A single customer can be enrolled in multiple programs and that would appear as multiple rows in the table - one for each enrollment date.

I'm trying to show (for a given program) how many of its enrollees are also enrolled in the other programs. Something like the following visual but where the cells are a number of customers that enrolled in both of the respective programs. I'm not sure where to start with calculating the cross-membership, let alone the visual I could use. Any help would be much appreciated!

[astotz]

Sorry, but I didn't see a way to attach a file to the post. Below is a paste of a small example. There is one record for each unique customer<->program enrollment. Results in this example would be a 3x3 matrix where the cells would be the % of the row enrolled in the column:

	P1	P2	P3
P1	100%	30%	0%
P2	37.5%	100%	0%
P3	0%	0%	100%

id	program	date
1	P1	1/20/2019
2	P1	1/18/2019
3	P1	1/16/2019
4	P1	1/14/2019
5	P1	1/12/2019
6	P1	1/10/2019
7	P1	1/8/2019
8	P1	1/6/2019
9	P1	1/4/2019
10	P1	1/2/2019
11	P2	1/20/2019
12	P2	1/20/2019
13	P2	1/22/2019
14	P2	1/22/2019
15	P2	1/22/2019
3	P2	1/20/2019
4	P2	1/22/2019
5	P2	1/22/2019
16	P3	1/22/2019
17	P3	1/22/2019
18	P3	1/22/2019

I appreciate the prompt responses!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[astotz]

Super helpful to see how this was done. You used some patterns I wasn't aware of. Thanks so much!

[Ashish_Mathur]

You are welcome.

[v-shex-msft]

HI @astotz,

I think matrix visual and r visual will suitable for your requirement, please share some sample data then we can test to create graph.

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Share your source data and also show the expected result.