Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B. Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns. The objective is to create a matrix like data structure (with courses appearing in both row and column labels) with numbers inside the matrix quantifying the “Number of students who opted for course A and C”. So, for all possible course combinations, one may want to know the number of students who opted for those combinations.
The description above can be extended to cases where buying behavior has to be analysed. A sore manager may want to know “How many people who buy Brand A also buy Brand B.”
Here’s a snapshot of the source data and expected result
The number 1 in cell H4 (and cell F6) means that there is only one student who opted for courses B and D. Likewise, 3 in cell H5 (and cell G6) means that 3 students opted for courses C and D.
You may refer to my Power Query and PowerPivot solution in this workbook. Power Query has been used for generating a dynamic list of Courses and Power Pivot has been used for writing the DAX formula for quantifying within the matrix.
Quantify combination courses opted by students
{ 0 Comments }