cancel
Showing results for
Did you mean:
Highlighted
Member

## 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

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

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

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

## Re: Calculating Uptime

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
12 REPLIES 12
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
Member

## 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

should i be changing how i sum up this first?

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
Member

## Re: Calculating Uptime

here is the link for the file

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,

Member

## Re: Calculating Uptime

@Ashish_Mathur  any thoughts?

thanks

Super User IV

## Re: Calculating Uptime

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Member

## 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

## Re: Calculating Uptime

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Member

## 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

Announcements

#### 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?

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

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