Need to calculate how many courses users consumed

[Umakh27]

Hi Team,

I am new to Power BI and figuring out how to calculate how many courses have been consumed by users. Could you please help me to figure this out?

I have 10 courses and 25 users. Each can take any number of courses.

Course A

Course B

Course C

Course D

Course E

Course F

Course G

Course H

Course I

Course J

Now, I need to measure how many users took all 10 courses, how many took 9 courses, ,…. How many of them not taken any course (0 course)...

Result (graph) I am looking for in Power BI. Below graph was done on excel.

Here, 13 users took all 10 courses, 1 user took 4 courses, 1 user took 3 courses…

I may also need to check how many took 1 to 5 course

[BA_Pete]

Hi @Umakh27 ,

Are you able to share an example of your data please?

It sounds like it may be as simple as grouping your table by attendee and counting the number of courses each has taken, but can't be sure without seeing how the table is structured.

Pete

[Umakh27]

Thanks Pete for helping out. Here is the sample data I could provide to you. Hope you can give some idea based on this.

Email	Course Name	Course Status
User1@xyz.com		No Course Taken
User2@xyz.com	Course B	Not Attempted
User2@xyz.com	Course D	Not Attempted
User2@xyz.com	Course C	In Progress
User2@xyz.com	Course A	In Progress
User3@xyz.com	Course A	In Progress
User4@xyz.com	Course A	In Progress
User5@xyz.com	Course A	Completed
User5@xyz.com	Course C	Completed
User5@xyz.com	Course D	Completed
User5@xyz.com	Course A	Completed
User6@xyz.com		No Course Taken
User7@xyz.com	Course A	In Progress
User7@xyz.com	Course C	Completed
User7@xyz.com	Course D	Completed

Now, from above data, I want to know how many have not taken any course. In above case it is 2 users. 

How many took just 1 course - In above case 2 users (user 3 and user 4 have took just 1 course)

Number of courses taken	Number of Users
0 course	2 users
1 course	2 users
2 courses	0 user
3 courses	1 user
4 courses	2 users

Expected graph

[BA_Pete]

+2 for clear presentation of data and expected outcomes.

If you're only using this dataset for this particular output, then I'd recommend grouping it in Power Query like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJ0qKis0kvOz1XSUVIAYr98Bef80qLiVIWQxOzUPKVYHYg6IyR1UAVOYOUlCo4lJam5BSWpKfgUu5Ci2BnI9MxTCCjKTy9KLS7Gp9QRh1Jj4pWaEK/UFLtS5/zcgpxUZE9hUehMrEIXYhVit9qMyBg1J97bWJRi9w0WhWi+iQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, #"Course Name" = _t, #"Course Status" = _t]),
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Course Name"}),
    addCourseTaken = Table.AddColumn(repBlankNull, "courseTaken", each if [Course Status] = "No Course Taken" then 0 else 1),
    groupRows = Table.Group(addCourseTaken, {"Email"}, {{"noofTaken", each List.Sum([courseTaken]), type number}})
in
    groupRows

I've taken the original example table then added these steps (ignore 'repBlankNull'):

1) addCourseTaken = Added new column ('courseTaken') that assigns a zero where [Course Status] = 'No Course Taken', otherwise a one.

2) groupRows = Grouped the table on [Email] with an aggregate column of SUM of [courseTaken].

Once sent to the data model, you just need to add a measure like this:

_noofUsers = COUNTROWS(courseTable)

And set up your visual like this:

If you need the original dataset for other calculations etc. then you'd actually want to do this all in DAX, but it's slightly more complicated so I'll wait to see whether this method works for you.

Pete

[Umakh27]

Thanks Pete. I want to use original dataset for other calculations as well. I have other different columns in the file.

[BA_Pete · Accepted solution]

No problem. On reflection, as you need to materialise a 'Number of courses taken' column for your x-axis, I think it would be simplest to stick with what we've done already, but change a couple of small things.

Keep your original table as it is, and create a second, grouped table, that just references your original table as the source:

let
    Source = OriginalCourseTable,
    repBlankNull = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Course Name"}),
    addCourseTaken = Table.AddColumn(repBlankNull, "courseTaken", each if [Course Status] = "No Course Taken" then 0 else 1),
    groupRows = Table.Group(addCourseTaken, {"Email"}, {{"noofTaken", each List.Sum([courseTaken]), type number}})
in
    groupRows

This gives us the grouped table we had for your visual, and retains the original too.

Now, as you have other columns, I'm assuming that you'll want to be able to slice and dice your report by different dimensions so change your measure to this:

_noofUsers = DISTINCTCOUNT(GroupedCourseTable[Email])

This now gives you the flexibility to include more columns in your grouped table so you can relate it to dimension tables and control slicing/filtering of both your original and grouped tables at the same time.

Hope this all makes sense.

Pete