cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
P0werB1User
Frequent Visitor

Using clustered chart column to display calculated measure

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!

 

1 ACCEPTED SOLUTION
P0werB1User
Frequent Visitor

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.

 

DateMonthMonthNoAvailableMins
01/11/2019 00:00November1900
02/11/2019 00:00November1900
03/11/2019 00:00November1900
04/11/2019 00:00November1900
05/11/2019 00:00November1900
06/11/2019 00:00November1900
07/11/2019 00:00November1900

 

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:
 

View solution in original post

4 REPLIES 4
P0werB1User
Frequent Visitor

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.

 

DateMonthMonthNoAvailableMins
01/11/2019 00:00November1900
02/11/2019 00:00November1900
03/11/2019 00:00November1900
04/11/2019 00:00November1900
05/11/2019 00:00November1900
06/11/2019 00:00November1900
07/11/2019 00:00November1900

 

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:
 

View solution in original post

Great @P0werB1User , glad you got that resolved!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




v-yuta-msft
Community Support
Community Support

@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
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors