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
Anonymous
Not applicable

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

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

15 REPLIES 15
Ashish_Mathur
Super User
Super User

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/
Anonymous
Not applicable

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

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/
Anonymous
Not applicable

@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

Anonymous
Not applicable

@Ashish_Mathur  any thoughts?

 

thanks

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/

Hi @Ashish_Mathur 

thanks for this example! I have looked into this but cannot explain the following:

When enabling Subtotals, the numbers seem off:

 

ThoSt_0-1626074375881.png

Isn't the calculation of the total the average across the row? If yes, then how can the actual total be lower than any of the values in the columns?

Is there a misunderstanding on my part?

Hi,

For that example (as old as 2 years), there may not have been a need to show subtotals.  that being said, the result in the Total column does seem wrong.  Start a new thread with your data, describe your question and show the expected result.


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

Hi @Ashish_Mathur ,

no problem just wanted to find out if I am misunderstanding something here or if it's really wrong.

I will create a new thread with my data 🙂 thanks!

Anonymous
Not applicable

@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

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/
Anonymous
Not applicable

@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

Hi,

I cannot tell what mistake you are committing.  Compare your file to the solution file i uploaded.


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

@Ashish_Mathur 

all good. the relationship between the tables wasnt correct in my file. i had fixed that and i have validated the data

thank you for the help

You are welcome.


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

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.