Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rayinOz
Helper III
Helper III

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.

 

snap.PNG

 

Thoughts on how to do this in PowerBI? Any help would be much appreciated.

 

Cheers,

 

Ray

3 ACCEPTED SOLUTIONS

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

 

cluster1.PNG

 

and here is the output

 

cluster2.PNG

 

You can change these formulas as per your data model and enhnace these as you feel like.

 

Cheers,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

condcol.PNG

 

Hope this will be helpful 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

Hey Ray,

 

Can you send screen show of some sample data, it will be easy to produce that way?

 

Thanks,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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).

 

sample data.PNG

 

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

 

cluster1.PNG

 

and here is the output

 

cluster2.PNG

 

You can change these formulas as per your data model and enhnace these as you feel like.

 

Cheers,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Also you can add conditional column in power bi to calculate value for "complete in complete" column, it is pretty staright forward.

 

here are the steps:

 

1. Click edit queries

2. Goto "Add Column" Tab

3. Select condition column

 

and that is where you put the condition to calculate this column

 

Cheers,

P

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I came up with the following but for some reason, it returns an error..

 

CourseStatusNew = IF(SEARCH('UoM-report'[Course Status],"Completed"),"Completed","Incomplete")

 

Basically what I want it to do is search the Course Status column and if it has a status of Completed, then the result is Completed, if it is anything other than complete (such as in progress or not yet started or extempted, etc) to set it as Incomplete.

 

Thoughts?

Here is what I think you need

CourseStatusNew = IF(SEARCH("Completed", 'UoM-report'[Course Status]),"Completed","Incomplete")


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hmm... still the same error.. i've attached a screenshot... The column i want to search is Course Status... the column next to it is a manual one that was done in Excel and that i want to do away with once i get the function to work correctly in Power Bi.

 

searchnreplace.PNG

 

 

 

 

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

condcol.PNG

 

Hope this will be helpful 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you so much!! Yes... that worked! You've helped TREMENDOUSLY!

My pleasure and glad it is working. good luck and never hesitate to ask. This community is great. Cheers!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks! My next adventure is comparing course completion rates by date ranges...

 

 

ray

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.