cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate Service/Network Availability in month and YTD

Dear All,

I have 2 tables

 TABLE 1 DEFECT LOCATION MTTR Month YEAR BRAND A 1 3.2 JAN 2019 BRAND B 1 4 JAN 2019 BRAND C 1 1 JAN 2019 BRAND A 2 2 JAN 2019 BRAND B 2 7 JAN 2019 BRAND C 2 1 JAN 2019 BRAND A 1 1 JAN 2019 BRAND A 2 5 FEB 2019 BRAND C 1 3.2 FEB 2019 BRAND A 1 4 FEB 2019 BRAND B 1 1 FEB 2019 BRAND C 1 2 FEB 2019 BRAND A 2 7 FEB 2019 BRAND B 2 1 FEB 2019 BRAND C 2 1 FEB 2019 BRAND A 1 5 FEB 2019 BRAND A 2 1 FEB 2019 BRAND C 1 3 FEB 2019

 Table 2 Brand Total of Brand Month Year BRAND A 121 FEB 2019 BRAND B 151 FEB 2019 BRAND C 93 FEB 2019 BRAND A 117 JAN 2019 BRAND B 145 JAN 2019 BRAND C 92 JAN 2019

How to calculate Service Availability base on the below formula.

SERVICE AVAILABILITY % = ((TOTAL OPERATING TIME - MTTR)/TOTAL OPERATING TIME)%

TOTAL OPERATING TIME = (TOTAL OF BRAND x 24 x Day in Month), Day in Month if Jan 2019 = 31day, Feb 2019 = 28day

Outage time = MTTR

 BRAND JAN FEB A 6.2 22 B 11 2 C 2 9.2

Total Operating Hours

 BRAND JAN FEB A 87048 81312 B 107880 101472 C 68448 62496

Output

 Brand SERVICE AVAILABILITY (JAN) SERVICE AVAILABILITY (FEB) SERVICE AVAILABILITY (YTD) Brand A 99.9929% 99.9729% 99.9829% Brand B 99.9898% 99.9980% 99.9939% Brand C 99.9971% 99.9853% 99.9912%

**Service Availability YTD = Average Service Availability Each Month

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: How to calculate Service/Network Availability in month and YTD

Hi @zairee

You may check the attached file.The key column can link the two tables.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team

## Re: How to calculate Service/Network Availability in month and YTD

Hi @zairee

Please check relationships in the attached file and create the measures as below.

`Day in Month = COUNT('Calendar'[Date])`
`TOTAL OPERATING TIME = SUM(Table2[Total of Brand])*24*[Day in Month]`
`SERVICE AVAILABILITY = DIVIDE( [TOTAL OPERATING TIME]-SUM(Table1[MTTR]),[TOTAL OPERATING TIME])`

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: How to calculate Service/Network Availability in month and YTD

chery,

Community Support Team

## Re: How to calculate Service/Network Availability in month and YTD

Hi @zairee

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: How to calculate Service/Network Availability in month and YTD

I have try but can't get the result. Maybe the actual data is not match with example. Thus, i have change my table base on my case. Kindly assist me.

Table 1

 MTTR EQUIPMENT_TYPE CAUSE_CATEGORY ITICKET_NE_BRAND MONTH WEEK YEAR NTT_CREATED_DATE HSBB/BAU 11 GROUP 1 Equipment Hardware BRAND 3 01 01 2018 01/01/2018 06:27:19 HSBB 0.47 GROUP 1 Tm Power BRAND 4 01 01 2018 01/01/2018 11:04:31 HSBB 2.59 GROUP 1 Tm Power BRAND 3 01 01 2018 01/01/2018 12:22:57 BAU 1.37 GROUP 2 Tm Power BRAND 3 01 01 2018 01/01/2018 15:56:09 BAU 44.51 GROUP 2 Equipment Hardware BRAND 3 01 01 2018 01/01/2018 21:31:56 BAU 3.46 GROUP 1 Equipment Software BRAND 4 01 01 2018 02/01/2018 11:11:02 BAU 8.4 GROUP 1 Equipment Hardware BRAND 4 01 01 2018 02/01/2018 21:05:00 BAU 4.16 GROUP 3 Equipment Software BRAND 6 01 01 2018 05/01/2018 11:43:46 BAU 1.43 GROUP 3 Equipment Software BRAND 5 01 02 2018 08/01/2018 19:13:54 BAU 6 GROUP 3 Equipment Hardware BRAND 5 01 03 2018 15/01/2018 07:31:11 BAU 10.27 GROUP 1 Tm Power BRAND 3 02 05 2018 01/02/2018 05:22:02 BAU 1.51 GROUP 1 Tm Power BRAND 4 02 05 2018 01/02/2018 07:35:42 HSBB 1.3 GROUP 1 Equipment Software BRAND 3 02 05 2018 01/02/2018 09:39:22 HSBB 2.38 GROUP 1 Equipment Hardware BRAND 3 02 05 2018 01/02/2018 10:31:29 HSBB 1.45 GROUP 1 Equipment Software BRAND 4 02 05 2018 01/02/2018 12:08:27 BAU 4.36 GROUP 1 Equipment Hardware BRAND 4 02 05 2018 01/02/2018 12:52:22 BAU 2.8 GROUP 1 Tm Power BRAND 4 02 05 2018 01/02/2018 17:04:36 BAU 21.42 GROUP 2 Equipment Software BRAND 3 02 05 2018 02/02/2018 15:18:10 BAU 2.1 GROUP 1 Tm Power BRAND 3 02 05 2018 02/02/2018 21:21:11 BAU 3.45 GROUP 3 Tm Power BRAND 2 02 05 2018 03/02/2018 09:01:27 BAU 7.31 GROUP 3 Equipment Hardware BRAND 5 02 08 2018 22/02/2018 11:42:43 BAU 1.14 GROUP 3 Equipment Software BRAND 6 02 08 2018 25/02/2018 21:17:59 BAU 1.59 GROUP 1 Tm Power BRAND 4 03 09 2018 01/03/2018 00:36:37 HSBB 1.56 GROUP 1 Equipment Hardware BRAND 4 03 09 2018 01/03/2018 07:20:30 BAU 22.16 GROUP 3 Equipment Hardware BRAND 2 03 09 2018 01/03/2018 18:00:32 BAU 1.25 GROUP 3 Equipment Software BRAND 2 03 09 2018 02/03/2018 09:28:57 BAU 5.23 GROUP 1 Tm Power BRAND 3 03 09 2018 02/03/2018 16:13:47 HSBB 2.3 GROUP 3 Tm Power BRAND 3 03 09 2018 02/03/2018 18:27:50 BAU 1.55 GROUP 1 Tm Power BRAND 3 03 09 2018 02/03/2018 19:15:24 BAU 1.42 GROUP 3 Tm Power BRAND 3 03 09 2018 04/03/2018 19:37:16 BAU 5.27 GROUP 3 Equipment Hardware BRAND 5 03 10 2018 07/03/2018 10:56:51 BAU 21.29 GROUP 3 Equipment Hardware BRAND 6 03 10 2018 10/03/2018 13:22:34 BAU 2.27 GROUP 3 Tm Power BRAND 6 03 13 2018 27/03/2018 21:22:06 BAU

Table 2

 ITICKET_NE_BRAND EQUIPMENT_TYPE HSBB/BAU Year Month TOTAL_NE BRAND 3 GROUP 1 HSBB 2018 3 1488 BRAND 4 GROUP 1 HSBB 2018 3 1200 BRAND 3 GROUP 2 HSBB 2018 3 573 BRAND 1 GROUP 2 HSBB 2018 3 191 BRAND 2 GROUP 3 BAU 2018 3 3582 BRAND 3 GROUP 3 BAU 2018 3 4701 BRAND 6 GROUP 3 BAU 2018 3 1077 BRAND 5 GROUP 3 BAU 2018 3 1711 BRAND 3 GROUP 2 BAU 2018 3 3000 BRAND 2 GROUP 2 BAU 2018 3 2702 BRAND 3 GROUP 1 BAU 2018 3 7351 BRAND 4 GROUP 1 BAU 2018 3 8542 BRAND 3 GROUP 1 HSBB 2018 2 1487 BRAND 4 GROUP 1 HSBB 2018 2 1199 BRAND 3 GROUP 2 HSBB 2018 2 572 BRAND 1 GROUP 2 HSBB 2018 2 191 BRAND 2 GROUP 3 BAU 2018 2 3581 BRAND 3 GROUP 3 BAU 2018 2 4700 BRAND 6 GROUP 3 BAU 2018 2 1076 BRAND 5 GROUP 3 BAU 2018 2 1710 BRAND 3 GROUP 2 BAU 2018 2 3100 BRAND 2 GROUP 2 BAU 2018 2 2701 BRAND 3 GROUP 1 BAU 2018 2 7340 BRAND 4 GROUP 1 BAU 2018 2 8541 BRAND 3 GROUP 1 HSBB 2018 1 1486 BRAND 4 GROUP 1 HSBB 2018 1 1198 BRAND 3 GROUP 2 HSBB 2018 1 571 BRAND 3 GROUP 2 HSBB 2018 1 3200 BRAND 1 GROUP 2 HSBB 2018 1 191 BRAND 2 GROUP 3 BAU 2018 1 3580 BRAND 3 GROUP 3 BAU 2018 1 4699 BRAND 6 GROUP 3 BAU 2018 1 1075 BRAND 5 GROUP 3 BAU 2018 1 1709 BRAND 3 GROUP 2 BAU 2018 1 3200 BRAND 2 GROUP 2 BAU 2018 1 2700 BRAND 3 GROUP 1 BAU 2018 1 7336 BRAND 4 GROUP 1 BAU 2018 1 8540

Day In Month = Formula the day in the month (Jan = 31, Feb=28, Mar = 31, April = 30)

Total Operation Time = 24*Day In month*Total NE

Service Availability = (Total Operation Time - MTTR)/(Total Operation Time)

Output

Community Support Team

## Re: How to calculate Service/Network Availability in month and YTD

Hi @zairee

You may check the attached file.The key column can link the two tables.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: How to calculate Service/Network Availability in month and YTD

Hi almost done, but data of MTTR not appear beside. I'm suspect on the relationship between table since my PBI jan 2019 cant enable many to many relationship. how to enable it?if cant, how to relationship this mttr

Community Support Team

## Re: How to calculate Service/Network Availability in month and YTD

Hi @zairee

I would suggest you use  to try again.If it is not your case,could you share the .pbix file for me to check?You can upload the .pbix file to OneDrive and post the link here or send me via private message. Do mask sensitive data before uploading.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: How to calculate Service/Network Availability in month and YTD

Hi Cherie_Chen, The problem solved. My mistake during make a relationship. Now the output success as expected. Very appreciated it. Thanks a lot.