Forum Discussion

m_roussakis's avatar
m_roussakis
Helper III
4 years ago
Solved

DAX help to count 100% completion

I have a matrix table which shows the training completion rate per region and employee.  I have about 8000 employees, each assigned multiple courses to complete.    As you can see from the scree...
Need Help
m_roussakis's avatar
m_roussakis
Helper III
4 years ago

I can't really share the data. However its quite a simple layout. 

 

I have:

 

Column A = User ID

Column B = Course Title

Column C = Course Completion status

Column D = Location

So for example a user with ID 12345 could have 10 courses showing in column B. Of these, 8 are complete.

 

I need to know which users have 100% of the assigned courses showing complete. 

 

And I don't need to know how to calculate the percentage of completed courses....that I already have. I need to be able to display the COUNT of 100% complete users per location as a value in a matrix table. 

 

Thanks

ryan_mayu's avatar
ryan_mayu
Super User
4 years ago

m_roussakis 

pls see the attachment below. It will be better if you can provide some dummy data and expected output

  • m_roussakis's avatar
    m_roussakis
    Helper III
    4 years ago

    This worked beautifully. Thanks.

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      4 years ago

      you are welcome

  • m_roussakis's avatar
    m_roussakis
    Helper III
    4 years ago

    I need to make an edit to the gtreat DAX you gave me.

    How can I EXCLUDE specific rows from this calculation. So I want to exclude some Course Titles:

     

    Column A = User ID

    Column B = Course Title (EXCEPT where title = "ABC123")

    Column C = Course Completion status

    Column D = Location

     

    Thanks

    • m_roussakis's avatar
      m_roussakis
      Helper III
      4 years ago

      I tried to do it myself and it's not working:

       

      Here's my attempt. My addition is shown underlined. 

       

      User Completion =
      VAR tbl=SUMMARIZE('Training','Training'[COUNTRY],Training[User - User ID],"completed",DIVIDE(CALCULATE(COUNTROWS('Training'),FILTER('Training','Training'[Transcript - Transcript Status]="Completed" || 'Training'[Training - Training Title]<>"MRS - Module 18: Additional Features - August 20220" )),CALCULATE(COUNTROWS('Training'),ALLEXCEPT('Training','Training'[User - User ID]))))
      return COUNTROWS(FILTER(tbl,[completed]=1)) +0
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        m_roussakis 

        maybe you can try this

        Measure =
        VAR tbl=SUMMARIZE('Table','Table'[Location],'Table'[User ID],"complete",DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Course Completion status]="Y" && 'Table'[Course Title]<>"ABC123")),CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[User ID]),'Table'[Course Title]<>"ABC123")))
        return COUNTROWS(FILTER(tbl,[complete]=1))
         
        pls see the attachment below