Goal by hour (include and exclude some hours)

[jcastr02]

Hello,

I have a goal measurement based on the number of people working during that hour.  However, we have some hours that have breaks included, How could I do a calculation to say   for 6am hour, goal is number of people X 7.5 (since we open at 6:30 and it's not a full hour).  There are other hours, but wasn't sure how to add that into my formula below.   Thank you.

[v-yalanwu-msft · Accepted solution]

Hi, @jcastr02 ;

You could create a measure as follow:

Measure = 
IF(MAX('Table'[Hour]) in {TIME(6,0,0),TIME(22,0,0)},SUM('Table'[# of People Working])*7.5,
IF(MAX('Table'[Hour]) in {TIME(10,0,0),TIME(14,0,0)},SUM('Table'[# of People Working])*10,
SUM('Table'[# of People Working])*15))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @jcastr02 ;

Sorry, I don't quite understand what you mean; Can you give some examples?

please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcastr02]

Hello @v-yalanwu-msft 

Normally the goal is the # of people working X 15 in each of the hour blocks. 

I'd like to do so that if its the 6am and 10pm shift, the goal is # of people working X 7.5, if it's the 10am, 2pm, and 6pm block, the goal is number of people working X 10,  all other hours are # of people working X 15.  I'd like to do it in a formula.  

# Produced	# of People Working	Hour	Goal
5	4	6am	30
7	10	7am	150
9	9	8am	135
8	6	9am	90
20	4	10am	40
34	4	11am	60
60	4	12pm	60
60	4	1pm	60
78	4	2pm	40
90	6	3pm	90
88	5	4pm	75
87	4	5pm	60
66	6	6pm	60
24	4	7pm	60
55	6	8pm	90
66	7	9pm	105
7	5	10pm	37.5