Using clustered chart column to display calculated measure

[P0werB1User]

Hello all, I recently started playing around with PowerBI this week & hit a wall in relation to creating a visual in relation to a specific metric. I am looking to create a clustered column chart to display service availability across each month. I am using a very simple data set extracted from our ticketing system, example below. 

Customer Number	Incident Number	Configuration Item	Category	Opened MonthYear	Outage Start	Outage End	TimeDiffMin
1609	1180302	ITSO Network	Configuration	Nov-19	01/11/2019 00:00	01/11/2019 17:30	1050
1609	1184776	ITSO Dashboard	Network	Nov-19	05/11/2019 08:30	05/11/2019 10:30	120
857	1197521	ITSO Reports	MSR	Nov-19	15/11/2019 07:00	15/11/2019 08:27	87
362	1202491	ITSO Invoices	IAQ	Nov-19	20/11/2019 06:30	20/11/2019 11:00	270
327	1214650	ITSO - Other	Citrix	Dec-19	03/12/2019 08:00	03/12/2019 20:00	720
534	1226429	ITSO - Other	Configuration	Dec-19	16/12/2019 13:30	16/12/2019 13:40	10
110	1234097	ITSO Reports	Application Server	Dec-19	27/12/2019 08:00	27/12/2019 20:00	720
1618	1243501	ITSO - Other	Citrix	Jan-20	09/01/2020 16:30	09/01/2020 21:45	315
1981	1253032	ITSO Online	Application Server	Jan-20	18/01/2020 09:00	18/01/2020 12:15	195
1727	1283160	ITSO Reports	MSR Reports	Feb-20	19/02/2020 07:00	19/02/2020 14:00	420
1750	1283747	ITSO - Other	-	Feb-20	19/02/2020 13:00	19/02/2020 15:50	170

I have been able to extract a value for the time to resolve using the timestamps between outage start & outage end.

TimeDiffMin = DATEDIFF('ServiceOps - Incident Mgmt'[Outage Start], 'ServiceOps - Incident Mgmt'[Outage End],MINUTE)

Our teams availability is 15 hours / 900 mins every day. I am looking to create a service availability % measure to reflect this. I can do this at the day level using the following measure:

Availability: (900 - ('ServiceOps - Incident Mgmt'[TimeDiffMin])) / 900

However, I am unable to do this across the month & reflect in a visual due to different number of days. Can someone please advise?

Thanks in advance!

[P0werB1User · Accepted solution]

Hi @Greg_Deckler @v-yuta-msft 

Apologies, I don't think I explained my ask clearly. I have since been able to resolve my issue. My ask was: 

1. To get an overall service level as a % for the month

2. Get a view of the same measure across the various workstreams.

What was lacking from my original dataset was essentially some calendar data & the service availability of the team across each day. I created a table, sample below, and created the relationship between a 'Opened MonthYear' column in each.

Date	Month	MonthNo	AvailableMins
01/11/2019 00:00	November	1	900
02/11/2019 00:00	November	1	900
03/11/2019 00:00	November	1	900
04/11/2019 00:00	November	1	900
05/11/2019 00:00	November	1	900
06/11/2019 00:00	November	1	900
07/11/2019 00:00	November	1	900

The measure I used to get this was:

Measure: 1 - (SUM('ServiceOps - Incident Mgmt'[TimeDiffMin])) / SUM(CalendarData[AvailableMins])

I'm sure there's a more efficient way of getting to the same outcome, but I think I got there eventually! Screenshot of output available at link below:

https://community.powerbi.com/t5/media/gallerypage/user-id/212923/album-id/77496/image-id/243444i9CB... 

[Greg_Deckler]

Great @P0werB1User , glad you got that resolved!

[v-yuta-msft]

@P0werB1User ,

---

However, I am unable to do this across the month & reflect in a visual due to different number of days. Can someone please advise?

---

Could you please share more details about your requirement and give the expected result?

Regards,

Jimmy Tao

[Greg_Deckler]

I *think* you want Open Tickets? If not let me know. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364