Need help with DAX to create a card identifying Count of employeeID with no sales

[hong88]

Hi there,

I am new to this community, but I have read through a number of solutions and it was of great help. 

I currently have a problem figuring out how to create a card to identify the count of employees with 0 sales for both years and have worked in the company for more than 1 year.

I created a little mock table with the relevant details.

I really hope someone can help me, I am truly at a loss at the moment. 

 

[v-zhengdxu-msft · Accepted solution]

Hi @hong88 

Please try this:

Here is the sample data:

'Table':

Then add a measure:

MEASURE =
VAR _vtable =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "_Sumsales",
                CALCULATE (
                    SUM ( 'Table'[Sales Amount] ),
                    'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] )
                ),
            "_MaxTenure",
                CALCULATE (
                    MAX ( 'Table'[Tenure] ),
                    'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] )
                )
        ),
        [_MaxTenure] > 1
            && [_Sumsales] = 0
    )
RETURN
    COUNTROWS ( SUMMARIZE ( _vtable, 'Table'[EmployeeID] ) )

The variable [_vtable] returns:

So the result is as follow:

Best Regards

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

[PabloVallejo12]

Did you try to use the summarize function, with a filter in sales amount = 0 ?

[hong88]

I tried, but im a bit confused because the IDs appear twice and have to be filtered together, meaning if ID 2233 has 0 sales in 2022, but has 9915 in sales in 2023, it should not appear as an ID without sales for both years. 

The only ones that should be counted are IDs with both years = 0 and tenure > 1. 

I tried using CALCULATE(countrows(table),
filter(values(table[SalesAmount]),
Calculate(SUM(table[SalesAmount])=0)

Basically what I got back are just the rows with 0, im not sure how to futher filter based on same employeeID for 2022 and 2023 = 0 and tenure >1. 

[PabloVallejo12]

Maybe you can create a measure for each sales year, and use it for filter

[hong88]

edit: I think ive partially solved it, still cant figure out how to group it by ID and filter it if both years = 0. But I've decided to filter twice by year and by tenure > 0. 

Thanks for your help, ur ideas helped me think of alternative ways to analyse. But if anyone still knows the DAX for the original problem, I would be really interested to find out too 😊

[Irwan]

hello @hong88 

please check if this accomodate your need.

for count employee with zero sales in both year, create a new measure with following DAX.

Count =
var _Sales = 
SUMMARIZE(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[EmployeeID],
            "Sum",
            SUM('Table'[Sales Amount])
        ),
        [Sum]=0
    ),
    'Table'[EmployeeID]
)
Return
COUNTROWS(_Sales)

if you want to do with SUMMARIZE, then just copy that _Sales DAX into new table.

Summarize = 
SUMMARIZE(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[EmployeeID],
            "Sum",
            SUM('Table'[Sales Amount])
        ),
        [Sum]=0
    ),
    'Table'[EmployeeID]
)

if you need another filter for Tenure>0, then just add that line inside the FILTER.

Hope this will help.

Thank you.

[hong88]

Thanks everyone for your suggestions! couldnt have been able to do it without ur help. 😊