Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Thanks in advance
Solved! Go to Solution.
Hi @zairee
You may check the attached file.The key column can link the two tables.
Regards,
Hi
New to forum, but I am trying to create a Service/Network Availability report, and data pretty much matches that used in this report, ask. I have managed to replicate to a point but I am having problems as report will only show months where there is TTR / outage, so will not show Total Operating Time for any items that have not had an outage, and for those that have, only for that month, hence Year To Date Total Operating Time maybe wrong
Regards
Gerry
Hi
New to forum, but I am trying to create a Service/Network Availability report, and data pretty much matches that used in this report, ask. I have managed to replicate to a point but I am having problems as report will only show months where there is TTR / outage, so will not show Total Operating Time for any items that have not had an outage, and for those that have, only for that month, hence Year To Date Total Operating Time maybe wrong
Regards
Gerry
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
chery,
file attached error when downlading
Hi @zairee
Please check the file again.
Regards,
Cherie
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
Hi @zairee
You may check the attached file.The key column can link the two tables.
Regards,
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
Hi @zairee
I would suggest you use latest version of Power BI Desktop 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,
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |