Forum Discussion
Finding Percent Complete by course by division
Hello All!
I need help creating a chart that shows completion rate for course enrolments. This is the percentage of course status marked "complete" by course and by division. The previous employee did this manually and i'm looking for Power BI to help me do this more automatically.
I have the following relevant data columns in my data table (I have other columns such as student name, email, manager, etc):
Division
Course Name
Course Status (The data is text that is either "Complete" or "Incomplete"
Each row in the data is a course enrolment
Here is a screen grab of a chart that was created manually in excel using copy/paste and then imported into PowerBI. However, i'm looking to do this within PowerBI.
Thoughts on how to do this in PowerBI? Any help would be much appreciated.
Cheers,
Ray
No worries, I created a dummy data at my end, i can send you the file with formulas in it, basically in nutshell this is what I did:
created 3 measures to calculate
Total Completed
Total Completed = CALCULATE(COUNT(Student[Student]),FILTER(Student, Student[Course Status]="Complete"))
Total Courses
Total = CALCULATE(COUNT(Student[Student]))
% Completed
% Completed = [Total Completed] / [Total]
Added a column chart and added the value as below
and here is the output
You can change these formulas as per your data model and enhnace these as you feel like.
Cheers,
P
You are in edit query, not writing DAX formula.
As mentioned previously, go to add column tab, click conditional column and it will stratight forward from there. Try it and let me know.
Hope this will be helpful :)
12 Replies
Hey Ray,
Can you send screen show of some sample data, it will be easy to produce that way?
Thanks,
P
Sure!
I've hidden some columns with personal data and to help fit the data on one screenshot. The column "completeincomplete" was created the converted some courses statuses that were other than Complete to Incomplete (done in excel but would be easier done in PowerBI but not sure how to do that).
Let me know if that helps,
Thanks again... Ray
No worries, I created a dummy data at my end, i can send you the file with formulas in it, basically in nutshell this is what I did:
created 3 measures to calculate
Total Completed
Total Completed = CALCULATE(COUNT(Student[Student]),FILTER(Student, Student[Course Status]="Complete"))
Total Courses
Total = CALCULATE(COUNT(Student[Student]))
% Completed
% Completed = [Total Completed] / [Total]
Added a column chart and added the value as below
and here is the output
You can change these formulas as per your data model and enhnace these as you feel like.
Cheers,
P
