Count the employees who achieved the target in all months

[yousefalsalem]

Hi,
I have this mesure to get the sales employees who achive the target in th 1st quarter.
I want only the people who achieved the target in all months. so if an meployee achieved the target in Jan only, or Jan & March, then it will not be counted .

The below meausre will give the result in image 1, where 5 employees only got 3 so they achevied the target in each month.

However, as soon as I add the emplyee name to teh table as in image 2, I will get only 2 employees which is wrong.
So, I wonder what is wrong and how to fix it to get the 5 employees at the end with tehir details. 

Q1 Passing product test =

    CALCULATE(

        COUNT(data[ID#]),

        FILTER(

            data,

            data[Product achievement %] >= 1 &&

            data[Service achievement %] >= 1

            &&

            data[Month] IN {"January", "February", "March" }

        )

    )

image 1

image 2

[v-kongfanf-msft · Accepted solution]

Hi @yousefalsalem ,

When you add the emplyee name to the table, the emplyee field acts as a filter for the formula. Based on your description, it needs to be grouped by emplyee. Refer to the modified formula below.

Q1 Passing product test = 
CALCULATE (
    COUNT ( data[ID#] ),
    FILTER (
        ALL(data),
        data[Product achievement %] >= 1
            && data[Service achievement %] >= 1
            && data[Month]
                IN { "January", "February", "March" }
                    && data[ID#] = MAX ( data[ID#] )
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

This is another solution ... please try this :

Here’s a step-by-step guide to solve this with DAX in Power BI:

1. Define the Target Months: You’ve already defined the target months correctly.

   VAR TargetMonths = {"January", "February", "March"}

2. Create a Calculated Column: You’ll need to create a calculated column in your data table to check if each row corresponds to one of the target months and has achieved the required percentages.

   Achieved = 
   IF(
       data[Month] IN TargetMonths && 
       data[Product achievement %] >= 1 && 
       data[Service achievement %] >= 1,
       1,
       0
   )

3. Summarize the Data: Use the SUMMARIZE function to group the data by employee and count the number of achieved months.

   EmployeeAchievements = 
   SUMMARIZE(
       data,
       data[Employee Name],
       "AchievedMonths", 
       SUM(data[Achieved])
   )

4. Filter the Summary: Now, filter this summarized table to only include employees who have achieved the target in all three months.

   QualifiedEmployees = 
   FILTER(
       EmployeeAchievements,
       [AchievedMonths] = 3
   )

5. Count the Rows: Finally, count the number of rows in the filtered table to get the number of employees who passed the product test.

   PassingProductTest = 
   COUNTROWS(QualifiedEmployees)

6. Create a Measure: Combine all the steps above into a single measure that you can use in your reports.

   Passing Product Test = 
   VAR TargetMonths = {"January", "February", "March"}
   VAR EmployeeAchievements = 
       SUMMARIZE(
           data,
           data[Employee Name],
           "AchievedMonths", 
           SUM(data[Achieved])
       )
   VAR QualifiedEmployees = 
       FILTER(
           EmployeeAchievements,
           [AchievedMonths] = 3
       )
   RETURN
   COUNTROWS(QualifiedEmployees)

This measure will now return the count of employees who have 100% product and service achievement in January, February, and March. Remember to replace data with the actual name of your table in Power BI. Also, ensure that the Product achievement % and Service achievement % are formatted correctly as percentages in the data model.

Let me know if you need further assistance! 😊

[123abc]

To achieve this in Power BI DAX, you need to count the employees who achieved the target in all three months of the first quarter. You can modify your DAX measure to achieve this.

Here's how you can do it:

Q1 Passing product test =
VAR TotalMonths = 3 -- Total number of months in the quarter
RETURN
COUNTROWS(
FILTER(
SUMMARIZE(
data,
data[Employee Name],
"AchievedMonths", COUNTAX(
FILTER(
ALL(data),
data[Product achievement %] >= 1 && data[Service achievement %] >= 1
),
data[Month]
)
),
[AchievedMonths] = TotalMonths
)
)

Explanation of the DAX measure:

1. SUMMARIZE is used to get a table with distinct employee names and the number of months they achieved the target.
2. FILTER is used to filter out the employees who achieved the target in all three months.
3. COUNTROWS counts the number of employees who achieved the target in all three months.

This measure should give you the correct count of employees who achieved the target in all three months of the first quarter.

Here's the modified DAX measure for your reference:

Q1 Passing product test =
VAR TotalMonths = 3 -- Total number of months in the quarter
RETURN
COUNTROWS(
FILTER(
SUMMARIZE(
data,
data[Employee Name],
"AchievedMonths", COUNTAX(
FILTER(
ALL(data),
data[Product achievement %] >= 1 && data[Service achievement %] >= 1
),
data[Month]
)
),
[AchievedMonths] = TotalMonths
)
)

Replace data[Employee Name] with the actual column name for the employee names in your dataset.

This modified measure should give you the correct count of employees who achieved the target in all three months of the first quarter, even when you add the employee names to the table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[yousefalsalem]

Thank but this give no result at all.

[123abc]

Please try this :

Q1 Passing product test =
VAR TargetMonths = {"January", "February", "March"}
RETURN
COUNTROWS(
FILTER(
SUMMARIZE(
data,
data[Employee Name],
"AchievedMonths",
COUNTROWS(
FILTER(
ALL(TargetMonths),
data[Month] IN TargetMonths &&
data[Product achievement %] >= 1 &&
data[Service achievement %] >= 1
)
)
),
[AchievedMonths] = 3
)
)