cancel
Showing results for
Did you mean:
New Member

## Need to calculate how many courses users consumed

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

1 ACCEPTED SOLUTION
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

5 REPLIES 5
Super User

Hi @Umakh27 ,

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

New Member

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

Super User

+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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

New Member

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

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors