DAX help to count 100% completion

[m_roussakis]

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 screenshot, user Kelly has 14 courses assigned to them, and 0% are complete.

I'd like to add a column which counts Total users who have an overall completion rate of 100%. So all courses they have assigned to them show complete

Thanks

[ryan_mayu]

@m_roussakis 

could you pls provide the sample data?

[m_roussakis]

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 · Accepted solution]

@m_roussakis 

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

[m_roussakis]

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]

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]

@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

[m_roussakis]

This worked beautifully. Thanks.

[ryan_mayu]

you are welcome