• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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


LearnerIDLearnerNameStart DatePlanned EndStatusEmployerIDEmployer nameCompletion DateWithdrawal 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 :)
 
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"
    )
)
 
Those are the formulas in the original post. :rolleyes:
 
Those are the formulas in the original post. :rolleyes:
My bad


Code:
In progress countx = COUNTX(VALUES(LearningPlan[EmployerID]), CALCULATE(COUNT(LearningPlan[EmployerID]), LearningPlan[Status] = "In Progress"))

In progress sumx = SUMX(VALUES(LearningPlan[EmployerID]), CALCULATE(SUM(LearningPlan[SomeNumericColumn]), LearningPlan[Status] = "In Progress"))
In the "In progress countx" script, it counts the rows where the status is "In Progress" for each distinct employer ID.In the "In progress sumx" script, it sums up a numeric column (replace "SomeNumericColumn" with the actual numeric column name from your dataset) where the status is "In Progress" for each distinct employer ID. Adjust the column name as per your dataset structure.
 
Back
Top