Forum Discussion

rayinOz's avatar
rayinOz
Helper III
9 years ago
Solved

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 employ...
Need Help
  • parry2k's avatar
    parry2k
    9 years ago

    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

  • parry2k's avatar
    parry2k
    9 years ago

    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.

  • parry2k's avatar
    parry2k
    9 years ago

     

    Hope this will be helpful :)

parry2k's avatar
parry2k
Super User
9 years ago

Hey Ray,

 

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

 

Thanks,

P

  • rayinOz's avatar
    rayinOz
    Helper III
    9 years ago

    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

    • parry2k's avatar
      parry2k
      Super User
      9 years ago

      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

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        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