Help on count

[harrinho]

Hi guys, 

I'm trying to find a solution on the following. I have a data table with multiple rows for an employee, with hours data on a monthly basis, for example: 

KEY	PERIOD	HOURS
AAA1	31/05/2018	240
AAA1	30/06/2018	200
AAA1	31/07/2018	200

Using further calculations and relationships from other tables which distinguish the type of hours (billable, non billable etc.), I create a calculation which generates a % based on the amount of these hours. Let's call it Utilization percentage. 

So when I create a a table in power bi bringing in the key and the Utilization percentage it looks like this 

KEY	Utilization %
AAA1	50%
AAA2	60%

What I want is to create a column in to the data table where if the Utilization % is less than 60% then put 1 otherwise 0. I create this column but it doesn't really work as it marks everything with 1 because it I guess it the calculation does't aggregate the Utilization % from every month. And it looks like the following (the instead of column is what I'd like to show)

KEY	Utilization %	Threshold flag	Instead of
AAA1	50%	1	0
AAA2	60%	1	1
AAA3	60%	1	1
AAA4	55%	1	0
AAA5	66%	1	1
AAA6	77%	1	1
AAA7	38%	1	0
AAA8	44%	1	0

I want in order to be able to get the count of the people with utilizaztion less than 60%

Any help would be much appreciated.

[v-danhe-msft]

Hi @harrinho,

could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @harrinho,

Based on my test, you can refer to below steps:

1.I have entered some sample data like the picture below:

2.Create a measure.

Measure = CALCULATE(COUNT(Table1[KEY]),FILTER('Table1','Table1'[Utilization %]<0.6))

3.Create a Card visual and add the related field. Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/0lomj77l5h104xq/Help%20on%20count.pbix?dl=0

Regards,

Daniel He