Wishmaster81
New Member
Hi All
This is probably a really simple issue but i can't get my head round it, please see my formulas and sample data below.
So both formulas will use VALUES to get each employer ID ,"3" in this case.
For each employer ID either use COUNTX or SUMX and then count how many learners are in progress. What is SUMX doing that COUNTX isn't doing? I would expect COUNTX to count 3 learners and SUMX to do the same. However in a matrix with employer name and these two measures, the SUMX formula total =3 and COUNTX formula total = 1.
Formulas
Sample data
This is probably a really simple issue but i can't get my head round it, please see my formulas and sample data below.
So both formulas will use VALUES to get each employer ID ,"3" in this case.
For each employer ID either use COUNTX or SUMX and then count how many learners are in progress. What is SUMX doing that COUNTX isn't doing? I would expect COUNTX to count 3 learners and SUMX to do the same. However in a matrix with employer name and these two measures, the SUMX formula total =3 and COUNTX formula total = 1.
Formulas
in progress countx = countx(VALUES(LearningPlan[EmployerID]),CALCULATE(COUNT(LearningPlan[EmployerID]),LearningPlan[Status] = "In Progress"))
in progress sumx = sumx(VALUES(LearningPlan[EmployerID]),CALCULATE(COUNT(LearningPlan[EmployerID]),LearningPlan[Status] = "In Progress"))
Sample data
LearnerID | LearnerName | Start Date | Planned End | Status | EmployerID | Employer name | Completion Date | Withdrawal Date |
9 | Marshall M | 03/09/2019 | 21/12/2020 | Withdrawn | 3 | Target | 10/10/2020 | |
10 | Barry K | 15/06/2023 | 02/10/2024 | Withdrawn | 3 | Target | 08/08/2023 | |
11 | Tony K | 30/04/2019 | 17/08/2020 | Withdrawn | 3 | Target | 29/05/2019 | |
12 | Emily D | 11/01/2020 | 30/04/2021 | Withdrawn | 3 | Target | 02/02/2020 | |
23 | Rob K | 01/01/2018 | 23/05/2023 | In Progress | 3 | Target | ||
24 | Jessy J | 15/03/2017 | 23/05/2023 | In Progress | 3 | Target | ||
25 | Chad M | 25/04/2017 | 28/02/2024 | In Progress | 3 | Target |