# Understanding countx and sumx

#### 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

``` 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

Hello

One Quick question to understand the issue better is: Are there any duplicate EmployerID values in the LearningPlan table, or is each EmployerID unique to a single employer?

Hello

One Quick question to understand the issue better is: Are there any duplicate EmployerID values in the LearningPlan table, or is each EmployerID unique to a single employer?
Their is only 1 employer id (3) in this table.

In a larger sample the employer id would be unique to a single employer yes,

Your CALCULATE statement calculates one value, so CountX on that returns 1 as it is counting the number of values. The actual value calculated is 3 and the sum of 3 is 3, so that's what sumx returns.

Your CALCULATE statement calculates one value, so CountX on that returns 1 as it is counting the number of values. The actual value calculated is 3 and the sum of 3 is 3, so that's what sumx returns.
Ahh I think I get it. So the calculate counts the employer as 3 which is 1 value so thats why countx returns as 1?

is sumx doing any calculations or is it just taking the 3 from the calculate statement?

Thanks for your assistance

It's basically doing SUM(3) in this instance.

It's basically doing SUM(3) in this instance.
I thought so , thanks.

Their is only 1 employer id (3) in this table.

In a larger sample the employer id would be unique to a single employer yes,

Here are the DAX formulas for both measures..try

Code:
``````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"
)
)``````

Here are the DAX formulas for both measures..try

Code:
``````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"
)
)``````
Those are the formulas in the original post.

Those are the formulas in the original post.
``````In progress countx = COUNTX(VALUES(LearningPlan[EmployerID]), CALCULATE(COUNT(LearningPlan[EmployerID]), LearningPlan[Status] = "In Progress"))