sum per group & total % for unique values

[Anonymous]

Hi all,

Straight to the issue. I need to create dashboards to track competencies on the project. I have data with hundreds of rows that in a simplified way looks like this:

Name	Role	Required course	Course completion date	Course repeat date
Nick G.	Engineer	Course X	21/09/2018	21/09/2020
Sam G.	Engineer	Course X
Sam G.	Manager	Course Y	04/04/2019	04/04/2021
Jack L.	Director	Course Z
Jack L.	Engineer	Course X
Kate R.	Accountant	Course M	23/06/2017	23/06/2019
Kate R.	Manager	Course Y	15/05/2019	15/05/2021
Kate R.	Engineer	Course X

Task 1. Calculate the number of confirmed specialists for each role. Note, that those who have completed all the required courses 100% are considered as confirmed specialists. As you might have noticed, more than one role can be assigned to a person. So, I should get the following numbers per role:

Role	No Confirmed SMEs	No SMEs per Role
Engineer	1	4
Manager	2	2
Director	0	1
Accountant	1	1

I’ve created the following measures:

Measure 1 “No SMEs per Role”: = CALCULATE(DISTINCTCOUNT(Table[Name], GROUPBY(Table, Table[Role]))  

Measure 2 ‘’No Confirmed SMEs’’ = IF ((COUNT(Table[Course completion date]/COUNT([Table[Required course]=1), DISTINCTCOUNT(Table[Name]),0)

??? Question 1 – how to combine two measures (or create a new one) to get the number of confirmed SMEs and not-confirmed SMEs per Role. 

Task 2. Calculate the total % of confirmed competencies.

To get % - ‘’No Confirmed SMEs’’/ “Total No of SMEs”

So, I’ve created:

Measure 3 “Total No of SMEs” = DISTINCTCOUNT(Table[Name])

Measure 4 “% of confirmed competencies’’= (SUMX(Measure 2 ‘’No Confirmed SMEs’’)/(Measure 3 “Total No of SMEs”))*100

??? Question 2 – Measure 4 gives me the wrong percentage.

Task 3. Highlight the course when there are 3 months left until the date of its re-completion (course repeat date).

??? Question 3 – what is the best way to do this?

Thanks in advance.

[StefanoGrimaldi]

question 3 try this: 

3 month alert = if( ((repetition date) - today())<=90, "alert, date near","not near" (or a condition to verify if the date alreay passes givien the subtraction get a negative value if the course was taken or not). 

question 2: 

try a more simply formular =

(calculate(count(table1[required courses],table1[course completition date]<>blank())  / count(table1[required courses]) ) *100

for the first question not sure what you looking for as you have a formula already that give you the number of confirmed people on each role just take that one a change it to count the opposite. or its giving you a wrong result?