cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Calculating Uptime

hi, looking for some help

 

trying to work out a uptime availability for a cloud service, i have working in excel but wish to move it over to powerbi to do all the calculations

 

Issue.PNG


for the month, if there are no incidents then the availability should be 100%

each month has a difference amount of minutes due to difference in days which i have a table

SR.PNG

 

 

if there is an issue calculate per month
- the number of total minutes for that month-the total downtime minutes for that month

- then divide the above figure by the

total minutes for that month

 

Service Availability =
DIVIDE(SUM('Service Reliability (days in month)'[HoursinMonth])-SUM('Issue'[Downtime]),(SUM('Service Reliability (days in month)'[HoursinMonth])))

 

this will give me a % availability but if there are is no downtime for that month, then the below matrix gives me blanks rather than  100%. i would like to show 100% in the empty slots below

 

OUtage.png

 

ive tried a few different isempty, isblank, using VAR, using + 0 at end of measure

all with no luck

 

im sure im doing somehting wrong. just dont know what

 

thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Calculating Uptime

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Super User IV
Super User IV

Re: Calculating Uptime

Hi,

Try this

=IF(ISBLANK(SUM('Issue'[Downtime])),1,DIVIDE(SUM('Service Reliability (days in month)'[HoursinMonth])-SUM('Issue'[Downtime]),(SUM('Service Reliability (days in month)'[HoursinMonth]))))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Re: Calculating Uptime

@Ashish_Mathur 

thanks for the help. i still get the same output.

i forgot to add the "Group" column in my example above which defines the service/product

 

would it be caused that i dont have a sum of that product group for that month? as shown in the sum of each month by "Group" matrix table. essentially the missing gaps means there were no downtime minutes for that month
SR2.png
should i be changing how i sum up this first?

Super User IV
Super User IV

Re: Calculating Uptime

Hi,

I am not sure of how much i can help but share the link from where i can download your PBI file.  In that file, please clearly show the result you are expecting to see.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Re: Calculating Uptime

@Ashish_Mathur 

 

here is the link for the file

https://drive.google.com/open?id=1E-OOUOH2GQhiswg6OBfyX3BsTTFsNEwl

 

the expected output which i have marked in red are all the blanks in the matrix reports that need a value

so 0.00 would be when i am calculating per month the sum of downtime. if there is no downtime, then i should see 0.00

 

when i work out the uptime % in the bottom table. i need the calculations to say 100% if there has been no affected downtime for that product for that month,

 

TEST.png

Re: Calculating Uptime

@Ashish_Mathur  any thoughts?

 

thanks

Super User IV
Super User IV

Re: Calculating Uptime

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Re: Calculating Uptime

@Ashish_Mathur 

thankyou. im trying to get an understand of the unpivot other columns step you applied. my real spreadsheet has many more columns. im trying to understand what i should be pivoting or not?

 

thanks

Super User IV
Super User IV

Re: Calculating Uptime

Hi,

Unpivot all columns other than Start time and End time columns.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Re: Calculating Uptime

@Ashish_Mathur 

thanks. i have the first 2 tables working great now

 

im doing something wrong when it comes to calculating the % table. the numbers do not seem to add up

cant seem to find what i have done

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors