Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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:
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:
Great @P0werB1User , glad you got that resolved!
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
I *think* you want Open Tickets? If not let me know. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |