Count number of days where measure equals value

[Luggruff]

Hi!

I'm trying to create a visual calculating bonus per employee, and each employee can earn bonus every day their productivity reaches a certain goal. I'm struggling with getting the number of days that the employees reach that target.

I have a measure calculating productivity by cases per hour, and it simply takes the sum of cases split by their number of working hours.

When I use this in a matrix that has employee and date as rows, it shows the cases per hour per employee and day.

Now, I want to create a table where I only have the employee name as rows, and instead a measure that "simply" (not so simple apparently) gives me the number of days where the cases per hour measure for each employee reaches a certain number in the data that I have filtered (have a slicer for month for example that is based on my calendar table). But I cannot figure out what logic to use for this. 

My data:

• Calendar DIM table
• Employee DIM table with employee ID and name
• FACT table with a column for handled cases, date and employee ID, although it has categories, so one employee might be listed multiple times per day. This is linked to the Calendar DIM table by the date column. It is also linked to the Employee DIM table by user ID in order to show users by name
• FACT table with working seconds column, date column and employee ID column

My cases per hour measure (based on two other measures):

Cases per hour = DIVIDE([Total cases handled],[Total working hours])

[Total cases handled] measure:

Total cases handled = SUMX('fact_Cases','fact_Cases'[Cases handled]))

[Total working hours] measure:

Total working hours = CALCULATE(([Total working time (sec)]/60)/60)

[Total working time (sec)] measure:

Total working time (sec) = CALCULATE((SUM('fact_Working_Hours'[Total of actual working hours (net) [Min]]])*60))

Any suggestions?

[Anonymous · Accepted solution]

Hi @Luggruff ,

I have created a simple sample, please refer to it to see if it helps you.

Create three measures.

workinghour = MAX('working hours'[Worked time (sec)])/60/60

Measure = var _1= MAX('fact'[Cases handled])/'working hours'[workinghour]
return
IF('working hours'[workinghour]=BLANK(),0,_1)

Measure 2 = var _1= COUNTAX(FILTER(ALL(Employee),Employee[Emplyee ID]=SELECTEDVALUE('fact'[Employee ID])&&[Measure]>=3.25),[Measure])
return
IF(_1=BLANK(),0,_1)

The relationship amont the tables like the following:

If I have misunderstood your meaning ,please provide more details with your desired output.

Best Regards
Community Support Team _ Polly

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

[vanessafvg]

are you able to supply some sample data in text form with an example of your expected outcome

[Luggruff]

Something like this:

dim_calendar:

Date	Month
2022-07-01	Jul 2022
2022-07-02	Jul 2022
2022-07-03	Jul 2022
2022-07-04	Jul 2022
2022-07-05	Jul 2022
2022-07-06	Jul 2022
2022-07-07	Jul 2022
2022-07-08	Jul 2022
2022-07-09	Jul 2022
2022-07-10	Jul 2022
2022-07-11	Jul 2022
2022-07-12	Jul 2022
2022-07-13	Jul 2022
2022-07-14	Jul 2022
2022-07-15	Jul 2022
2022-07-16	Jul 2022
2022-07-17	Jul 2022
2022-07-18	Jul 2022
2022-07-19	Jul 2022
2022-07-20	Jul 2022
2022-07-21	Jul 2022
2022-07-22	Jul 2022
2022-07-23	Jul 2022
2022-07-24	Jul 2022
2022-07-25	Jul 2022
2022-07-26	Jul 2022
2022-07-27	Jul 2022
2022-07-28	Jul 2022
2022-07-29	Jul 2022
2022-07-30	Jul 2022
2022-07-31	Jul 2022

dim_employees:

Emplyee ID	Name
542524	Employee A
542523	Employee B

fact_cases:

Date	Employee ID	Category	Cases handled
2022-07-15	542524	A	25
2022-07-18	542524	A	26
2022-07-25	542524	A	12
2022-07-25	542524	B	14
2022-07-14	542523	A	10
2022-07-15	542523	A	9
2022-07-15	542523	B	15
2022-07-20	542523	A	13

fact_Working Hours:

Date	Employee ID	Worked time (sec)
2022-07-14	542524	28800
2022-07-15	542524	28800
2022-07-15	542523	28800
2022-07-18	542524	28800
2022-07-20	542523	28800

Then expected output visual table would be:

Employee name	Days with X cases per hour
Empolyee A	1
Employee B	0

..given that cases per hour target per day is 3.25 (so the days where Employee A reached 26 cases total in the 8 hours. 

Note: It cannot be the count of days with numbers split by the cases per hour or anything, as the bonus is based on individual days.