Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |