Sum of a Count with Filter

[MissBI_21]

Hi,

I have a table in PowerBI that has a list of employees that complete a certain number of jobs in a day (shown below):

I have done a count on the Job_ID field to get the data in the second column.

What I want to be able to do is count the number of times an employee has completed 2 or more jobs in a day but have it shown by month. So the example above should look like below:

Can anyone help please?

[Jihwan_Kim]

Count Condition =

CALCULATE( COUNTROWS(Data), Data[Count of job_id]>=2)

https://www.dropbox.com/s/gdbckcv0w6m41px/missbi.pbix?dl=0 

[selimovd]

Hey @MissBI_21 ,

you can do that with that.

The following measure is just considering the rows where the count of job_id is 2 or bigger:

Count 2 or more jobs =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[Count of job_id] >= 2
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[MissBI_21]

Sorry, I didn't make it quite clear.

At the moment, I got the count of job_id by selecting count in the value field.

The original data I have looks like the below:

From this data, I want to do a count of jobs by day to see how many people work on more than one job in a day. and then I want to sum this too look like the second pic I sent originally.

So I would probably need a measure to do a count by day and emloyee, then I would filter it show a count of Job_IDs>2

[v-kelly-msft · Accepted solution]

Hi  @MissBI_21 ,

Create a measure as below:

Measure = 
SUMX(SUMMARIZE('Table','Table'[first name ],'Table'[Month],'Table'[Day],"total",IF('Table'[count of job_id]>=2,1,0)),[total])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!