Aggregate-able calculated measure/column based on value of another measure/column

[Anonymous]

I have data of a list of employees with their overtime data by event date. I also have a date table with eventdate grouped into work week. I've created a calculated measure to calculate their overtime hour by work week as below:

Week OT = sumx(SUMMARIZE('Data Table','Data Table'[Employee Name],'Date Table'[Work Week],"Week OT hrs",SUM('Data Table'[OT hrs])),[Week OT hrs])

This measure works fine and filterable by work week/employee name.

I now need to create a measure to flag if the person's weekly overtime is over 20hrs.

Flag = IF([Week OT]>20,1,0)

My final purpose is to count the number of flags for each work week and this won't work because it can't be aggregated like the Week OT measure (see picture). It only evaluate the grand total and flag that as "1"

How do I do this? Thanks in advance for your help!

[BILASolution · Accepted solution]

Hi @Anonymous

I hope this helps...

Measure = COUNTX
(
	FILTER
	(
		SUMMARIZE
		(
			'Data Table',
			'Data Table'[Employee Name],
			'Date Table'[Work Week],
			"Week OT hrs",SUM('Data Table'[OT hrs])
		),[Week OT hrs]>20
	),'Date Table'[Work Week]
)

Regards

BILASolution

[Anonymous]

Thank you both for your prompt help!! So @BILASolution's formula which used combo of COUNTX and SUMMARIZE works wonder for my case! The FILTER was used right after the SUMMARIZE function to clean the table right away before counting so that works perfect. I tried using COUNTROWS nested under CALCULATE to filter the ">20" part but didn't get too far. Thanks again!

[Ashish_Mathur]

Hi,

Try this

=IF(HASONEVALUE('Data Table'[Employee Name]),IF([Week OT]>20,1,0),CALCULATE(COUNTROWS('Data Table'),FILTER('Data table',[Week OT]>25)))

Does this work?