Compare fixed value vs sum of values

[leejoel]

Hello,

I am building a report where I need to compare the monthly sum (actual #) against a fixed goal (target #). I tried it out on my own but I'm not quite there.

TEST = IF(TOTALMTD(SUM('Sheet1'[ACTUAL HRS]))>'Sheet1'[TARGET HRS],1,0)

This results in an ERROR. Hoping someone can help?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  Do you need a calculated column or a measure solution?

[leejoel]

@Ashish_Mathur @amitchandak  Thanks for the replies. I am attaching a small sample of the data and hoping you can use it to help me. Every day there are downtime occurrences that add up (in minutes) and we need to compare the SUM by location against a fixed monthly target. See below and let me know if you need any more info from me.

Location 1 Monthly Target: 9.425 hours

Location 2 Monthly Target: 9.025 hours

Location 3 Monthly Target: 15.60 hours

LocationStart_TimeEnd_TimeMinutes of Downtime

1	1/9/2020 9:48:14 PM	1/9/2020 9:50:47 PM	2
1	1/9/2020 8:49:08 PM	1/9/2020 9:06:49 PM	17
3	1/9/2020 4:44:57 PM	1/9/2020 5:06:29 PM	22
2	1/9/2020 2:33:13 PM	1/9/2020 3:33:44 PM	60
2	1/8/2020 2:33:30 PM	1/8/2020 2:33:58 PM	0
2	1/8/2020 10:23:06 AM	1/8/2020 2:13:54 PM	230
2	1/8/2020 9:32:42 AM	1/8/2020 9:36:34 AM	4
2	1/7/2020 3:13:12 PM	1/7/2020 3:50:16 PM	37
3	1/7/2020 2:38:18 PM	1/7/2020 3:43:08 PM	65
3	1/7/2020 12:46:42 PM	1/7/2020 1:16:07 PM	30
3	1/7/2020 4:14:40 AM	1/7/2020 4:21:09 AM	7
2	1/6/2020 8:38:14 PM	1/6/2020 8:41:07 PM	3
2	1/6/2020 7:55:26 PM	1/6/2020 7:58:19 PM	3
2	1/6/2020 5:19:10 PM	1/6/2020 5:27:23 PM	8
3	1/6/2020 1:39:55 PM	1/6/2020 3:16:11 PM	97
2	1/6/2020 12:57:08 PM	1/6/2020 1:00:23 PM	3
3	1/6/2020 9:56:46 AM	1/6/2020 10:37:30 AM	41
3	1/5/2020 6:06:42 PM	1/5/2020 6:09:29 PM	3
2	1/4/2020 10:16:24 PM	1/4/2020 10:23:21 PM	7
2	1/4/2020 8:30:09 PM	1/4/2020 8:35:33 PM	5
3	1/3/2020 11:28:34 AM	1/3/2020 12:06:14 PM	38
1	1/3/2020 9:19:03 AM	1/3/2020 9:23:14 AM	4
1	1/3/2020 9:12:24 AM	1/3/2020 9:16:54 AM	4
1	1/3/2020 9:06:54 AM	1/3/2020 9:10:12 AM	4
2	1/3/2020 6:02:02 AM	1/3/2020 6:06:43 AM	4
3	1/3/2020 1:10:35 AM	1/3/2020 4:22:49 AM	192
1	1/2/2020 2:27:38 PM	1/3/2020 2:27:25 AM	720
1	1/2/2020 2:19:31 PM	1/2/2020 2:21:58 PM	2
1	1/2/2020 2:11:28 PM	1/2/2020 2:18:59 PM	7
1	1/2/2020 1:38:54 PM	1/2/2020 2:08:39 PM	30
1	1/2/2020 3:17:09 AM	1/2/2020 3:57:12 AM	40
2	1/2/2020 1:20:59 AM	1/2/2020 1:23:22 AM	3
2	1/2/2020 12:06:47 AM	1/2/2020 12:12:51 AM	6
1	1/1/2020 8:25:46 PM	1/1/2020 9:13:39 PM	48
2	1/1/2020 7:51:33 PM	1/1/2020 7:53:58 PM	2
2	1/1/2020 6:31:38 PM	1/1/2020 7:05:15 PM	34
1	1/1/2020 6:17:04 PM	1/1/2020 6:22:03 PM	5

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[leejoel]

@Ashish_MathurThank you. I unzipped the file and the file reads corrupt or invalid.

[v-easonf-msft]

Hi， @leejoel 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.

Best Regards,
Community Support Team _ Eason

[Ashish_Mathur]

It is .pbix file.  Why are you unzipping it?

[amitchandak]

Total MTD need a date column

try like

TEST = IF(TOTALMTD(SUM('Sheet1'[ACTUAL HRS]),'Date'[Date])> sum('Sheet1'[TARGET HRS]),1,0)

Assume a date table here

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[leejoel]

I tried this and everything comes back as a '0' value.