Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zairee
Frequent Visitor

How to calculate Service/Network Availability in month and YTD

Dear All,

I have 2 tables 

 

TABLE 1    
DEFECTLOCATIONMTTRMonthYEAR
BRAND A13.2JAN2019
BRAND B14JAN2019
BRAND C11JAN2019
BRAND A22JAN2019
BRAND B27JAN2019
BRAND C21JAN2019
BRAND A11JAN2019
BRAND A25FEB2019
BRAND C13.2FEB2019
BRAND A14FEB2019
BRAND B11FEB2019
BRAND C12FEB2019
BRAND A27FEB2019
BRAND B21FEB2019
BRAND C21FEB2019
BRAND A15FEB2019
BRAND A21FEB2019
BRAND C13FEB2019

 

Table 2   
BrandTotal of BrandMonthYear
BRAND A121FEB2019
BRAND B151FEB2019
BRAND C93FEB2019
BRAND A117JAN2019
BRAND B145JAN2019
BRAND C92JAN2019

 

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 JANFEB
A6.222
B112
C29.2

 

Total Operating Hours

BRAND JANFEB
A8704881312
B107880101472
C68448

62496

 

 

Output

 

Brand SERVICE AVAILABILITY (JAN)SERVICE AVAILABILITY (FEB)SERVICE AVAILABILITY (YTD)
Brand A99.9929%99.9729%99.9829%
Brand B99.9898%99.9980%99.9939%
Brand C99.9971%99.9853%99.9912%

 

**Service Availability YTD = Average Service Availability Each Month

 

Thanks in advance

1 ACCEPTED SOLUTION

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.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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

Anonymous
Not applicable

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

v-cherch-msft
Employee
Employee

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])

1.png

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.

chery,

file attached error when downlading

Hi @zairee 

 

Please check the file again.

 

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.

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

MTTREQUIPMENT_TYPECAUSE_CATEGORYITICKET_NE_BRANDMONTHWEEKYEARNTT_CREATED_DATEHSBB/BAU
11GROUP 1Equipment HardwareBRAND 30101201801/01/2018 06:27:19HSBB
0.47GROUP 1Tm PowerBRAND 40101201801/01/2018 11:04:31HSBB
2.59GROUP 1Tm PowerBRAND 30101201801/01/2018 12:22:57BAU
1.37GROUP 2Tm PowerBRAND 30101201801/01/2018 15:56:09BAU
44.51GROUP 2Equipment HardwareBRAND 30101201801/01/2018 21:31:56BAU
3.46GROUP 1Equipment SoftwareBRAND 40101201802/01/2018 11:11:02BAU
8.4GROUP 1Equipment HardwareBRAND 40101201802/01/2018 21:05:00BAU
4.16GROUP 3Equipment SoftwareBRAND 60101201805/01/2018 11:43:46BAU
1.43GROUP 3Equipment SoftwareBRAND 50102201808/01/2018 19:13:54BAU
6GROUP 3Equipment HardwareBRAND 50103201815/01/2018 07:31:11BAU
10.27GROUP 1Tm PowerBRAND 30205201801/02/2018 05:22:02BAU
1.51GROUP 1Tm PowerBRAND 40205201801/02/2018 07:35:42HSBB
1.3GROUP 1Equipment SoftwareBRAND 30205201801/02/2018 09:39:22HSBB
2.38GROUP 1Equipment HardwareBRAND 30205201801/02/2018 10:31:29HSBB
1.45GROUP 1Equipment SoftwareBRAND 40205201801/02/2018 12:08:27BAU
4.36GROUP 1Equipment HardwareBRAND 40205201801/02/2018 12:52:22BAU
2.8GROUP 1Tm PowerBRAND 40205201801/02/2018 17:04:36BAU
21.42GROUP 2Equipment SoftwareBRAND 30205201802/02/2018 15:18:10BAU
2.1GROUP 1Tm PowerBRAND 30205201802/02/2018 21:21:11BAU
3.45GROUP 3Tm PowerBRAND 20205201803/02/2018 09:01:27BAU
7.31GROUP 3Equipment HardwareBRAND 50208201822/02/2018 11:42:43BAU
1.14GROUP 3Equipment SoftwareBRAND 60208201825/02/2018 21:17:59BAU
1.59GROUP 1Tm PowerBRAND 40309201801/03/2018 00:36:37HSBB
1.56GROUP 1Equipment HardwareBRAND 40309201801/03/2018 07:20:30BAU
22.16GROUP 3Equipment HardwareBRAND 20309201801/03/2018 18:00:32BAU
1.25GROUP 3Equipment SoftwareBRAND 20309201802/03/2018 09:28:57BAU
5.23GROUP 1Tm PowerBRAND 30309201802/03/2018 16:13:47HSBB
2.3GROUP 3Tm PowerBRAND 30309201802/03/2018 18:27:50BAU
1.55GROUP 1Tm PowerBRAND 30309201802/03/2018 19:15:24BAU
1.42GROUP 3Tm PowerBRAND 30309201804/03/2018 19:37:16BAU
5.27GROUP 3Equipment HardwareBRAND 50310201807/03/2018 10:56:51BAU
21.29GROUP 3Equipment HardwareBRAND 60310201810/03/2018 13:22:34BAU
2.27GROUP 3Tm PowerBRAND 60313201827/03/2018 21:22:06

BAU

 

Table 2

ITICKET_NE_BRANDEQUIPMENT_TYPEHSBB/BAUYearMonthTOTAL_NE
BRAND 3 GROUP 1HSBB201831488
BRAND 4GROUP 1HSBB201831200
BRAND 3 GROUP 2HSBB20183573
BRAND 1GROUP 2HSBB20183191
BRAND 2GROUP 3BAU201833582
BRAND 3 GROUP 3BAU201834701
BRAND 6GROUP 3BAU201831077
BRAND 5GROUP 3BAU201831711
BRAND 3 GROUP 2BAU201833000
BRAND 2GROUP 2BAU201832702
BRAND 3 GROUP 1BAU201837351
BRAND 4GROUP 1BAU201838542
BRAND 3 GROUP 1HSBB201821487
BRAND 4GROUP 1HSBB201821199
BRAND 3 GROUP 2HSBB20182572
BRAND 1GROUP 2HSBB20182191
BRAND 2GROUP 3BAU201823581
BRAND 3 GROUP 3BAU201824700
BRAND 6GROUP 3BAU201821076
BRAND 5GROUP 3BAU201821710
BRAND 3 GROUP 2BAU201823100
BRAND 2GROUP 2BAU201822701
BRAND 3 GROUP 1BAU201827340
BRAND 4GROUP 1BAU201828541
BRAND 3 GROUP 1HSBB201811486
BRAND 4GROUP 1HSBB201811198
BRAND 3 GROUP 2HSBB20181571
BRAND 3 GROUP 2HSBB201813200
BRAND 1GROUP 2HSBB20181191
BRAND 2GROUP 3BAU201813580
BRAND 3 GROUP 3BAU201814699
BRAND 6GROUP 3BAU201811075
BRAND 5GROUP 3BAU201811709
BRAND 3 GROUP 2BAU201813200
BRAND 2GROUP 2BAU201812700
BRAND 3 GROUP 1BAU201817336
BRAND 4GROUP 1BAU201818540

 

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)

 

OutputSA.jpg

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.

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

MTTR.jpg

 

Relationship.jpg

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,

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.