Calculate the uptime in Pivot Table (Network Steve Forum)

Calculate the uptime in Pivot Table

Dear all,

I have an excel which keeps the system downtime details of each issue.

I want to use the Pivot table to summarize the data, and calculate the uptime % of each month.

The problem is, from Pivot table, I can only get the total down time, total downtime %, and the total uptime.

I need calculate the uptime % by (Total Uptime - Total Downtime)/Total Uptime * 100%.

How can I do so in Pivot Table?

Please help.

Ivan

May 6th, 2015 3:37am

Hi Lvan,

According to your description, my understanding is that you have two request of Pivot Table:

1) Summarize the data by each month

2) Calculate the uptime percent

Am I right? If yes, please we might try the methods below:

1) Grouping data in a PivotTable can help we summarize the data by each month. Steps:

In the PivotTable, right-click any numeric or date and time field, and click Group.
In the Starting at and Ending at box, enter this (as needed):Grouping dialog box.
The entry in the Ending at box should be larger or later than the entry in the Starting at box.
You can click additional time periods to group by. For example, you can group by Months and Weeks. Group items by weeks first, making sure Days is the only time period selected. In the Number of days box, click 7, and then click Months.

https://support.office.com/en-in/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725

2) We can use calculated fields to calculate the uptime percent, for example:

Total Uptime - Total Downtime)/Total Uptime (Show value as percent)

https://support.office.com/en-nz/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8

If I misunderstood something, please feel free let me know.

Regards,

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator 1 hour 14 minutes ago

Free Windows Admin Tool Kit Click here and download it now

May 7th, 2015 2:17am

Hi Lvan,

According to your description, my understanding is that you have two request of Pivot Table:

1) Summarize the data by each month

2) Calculate the uptime percent

Am I right? If yes, please we might try the methods below:

1) Grouping data in a PivotTable can help we summarize the data by each month. Steps:

In the PivotTable, right-click any numeric or date and time field, and click Group.
In the Starting at and Ending at box, enter this (as needed):Grouping dialog box.
The entry in the Ending at box should be larger or later than the entry in the Starting at box.
You can click additional time periods to group by. For example, you can group by Months and Weeks. Group items by weeks first, making sure Days is the only time period selected. In the Number of days box, click 7, and then click Months.

https://support.office.com/en-in/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725

2) We can use calculated fields to calculate the uptime percent, for example:

Total Uptime - Total Downtime)/Total Uptime (Show value as percent)

https://support.office.com/en-nz/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8

If I misunderstood something, please feel free let me know.

Regards,

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Thursday, May 07, 2015 6:15 AM

May 7th, 2015 6:15am

Hi Lvan,

According to your description, my understanding is that you have two request of Pivot Table:

1) Summarize the data by each month

2) Calculate the uptime percent

Am I right? If yes, please we might try the methods below:

1) Grouping data in a PivotTable can help we summarize the data by each month. Steps:

In the PivotTable, right-click any numeric or date and time field, and click Group.
In the Starting at and Ending at box, enter this (as needed):Grouping dialog box.
The entry in the Ending at box should be larger or later than the entry in the Starting at box.
You can click additional time periods to group by. For example, you can group by Months and Weeks. Group items by weeks first, making sure Days is the only time period selected. In the Number of days box, click 7, and then click Months.

https://support.office.com/en-in/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725

2) We can use calculated fields to calculate the uptime percent, for example:

Total Uptime - Total Downtime)/Total Uptime (Show value as percent)

https://support.office.com/en-nz/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8

If I misunderstood something, please feel free let me know.

Regards,

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Thursday, May 07, 2015 6:15 AM

Free Windows Admin Tool Kit Click here and download it now

May 7th, 2015 6:15am

Hi Lvan,

According to your description, my understanding is that you have two request of Pivot Table:

1) Summarize the data by each month

2) Calculate the uptime percent

Am I right? If yes, please we might try the methods below:

1) Grouping data in a PivotTable can help we summarize the data by each month. Steps:

In the PivotTable, right-click any numeric or date and time field, and click Group.
In the Starting at and Ending at box, enter this (as needed):Grouping dialog box.
The entry in the Ending at box should be larger or later than the entry in the Starting at box.
You can click additional time periods to group by. For example, you can group by Months and Weeks. Group items by weeks first, making sure Days is the only time period selected. In the Number of days box, click 7, and then click Months.

https://support.office.com/en-in/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725

2) We can use calculated fields to calculate the uptime percent, for example:

Total Uptime - Total Downtime)/Total Uptime (Show value as percent)

https://support.office.com/en-nz/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8

If I misunderstood something, please feel free let me know.

Regards,

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Thursday, May 07, 2015 6:15 AM

May 7th, 2015 6:15am

I have a raw table keep the date, year/month, downtime in hour, and total time of that month.

In pivot table, I summarize the data by year month, and get the total downtime of that month, and use average total time to get the total time of that month.

I just don't know how to calculate the uptime% by (1-total downtime/average total time) in Pivot table.

I have tried in the calculated field but the result is note the same as I expected.

Ivan

Free Windows Admin Tool Kit Click here and download it now

May 7th, 2015 10:32pm

Do you mind sharing us a sample file? It would be better to help us and resolve this issue.

Regards,

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator 22 hours 9 minutes ago

May 8th, 2015 5:16am

Dear George,

Please find the sample as below:

Raw data:

Result:

The yellow column is the data I want.

I need calculate the uptime % for 16 factory every month.

I cannot affort to manually do the formula every time.

Ivan

Free Windows Admin Tool Kit Click here and download it now

May 8th, 2015 5:54am

Do you mind sharing us a sample file? It would be better to help us and resolve this issue.

Regards,

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Friday, May 08, 2015 9:19 AM

May 8th, 2015 9:15am

Do you mind sharing us a sample file? It would be better to help us and resolve this issue.

Regards,

Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Friday, May 08, 2015 9:19 AM

Free Windows Admin Tool Kit Click here and download it now

May 8th, 2015 9:15am

Excel 2010 with free PowerPivot and Power Query Add-In.
Compatible with Office 2013 Pro Plus.
http://www.mediafire.com/view/dnwyfavlwa4rsp2/05_08_15.xlsx
http://www.mediafire.com/view/5tqvgv2ca4k5lla/05_08_15.pdf

Edited by Herbert Seidenberg 16 hours 13 minutes ago

May 8th, 2015 11:01am

Edited by Herbert Seidenberg Friday, May 08, 2015 3:14 PM

Free Windows Admin Tool Kit Click here and download it now

May 8th, 2015 3:01pm

Edited by Herbert Seidenberg Friday, May 08, 2015 3:14 PM

May 8th, 2015 3:01pm

Is that I must use third party plug-in to do my calculation?

Ivan

Free Windows Admin Tool Kit Click here and download it now

May 13th, 2015 5:56am

PowerPivot and Power Query are integral parts of Excel 2013 Pro Plus,
and not backwards compatible.
The free versions (from Microsoft) in Excel 2010 are just come-ons.

May 13th, 2015 3:39pm

Hi Lvan,

I agree with Herbert Seidenberg, we might use PowerPivot to get your goal quickly and easily.

PowerPivot and Power Query is provided by Microsoft, we can download it from here. (It's the free versions for Excel 2010 )

https://support.office.com/en-nz/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

Regards,

Free Windows Admin Tool Kit Click here and download it now

May 13th, 2015 9:22pm

Hi Lvan,

I agree with Herbert Seidenberg, we might use PowerPivot to get your goal quickly and easily.

PowerPivot and Power Query is provided by Microsoft, we can download it from here. (It's the free versions for Excel 2010 )

https://support.office.com/en-nz/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

Regards,

Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 1 hour 42 minutes ago

May 14th, 2015 1:21am

Hi Lvan,

I agree with Herbert Seidenberg, we might use PowerPivot to get your goal quickly and easily.

PowerPivot and Power Query is provided by Microsoft, we can download it from here. (It's the free versions for Excel 2010 )

https://support.office.com/en-nz/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

Regards,

Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Friday, May 29, 2015 5:48 AM

Free Windows Admin Tool Kit Click here and download it now

May 14th, 2015 1:21am

Hi Lvan,

I agree with Herbert Seidenberg, we might use PowerPivot to get your goal quickly and easily.

PowerPivot and Power Query is provided by Microsoft, we can download it from here. (It's the free versions for Excel 2010 )

https://support.office.com/en-nz/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

Regards,

Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Friday, May 29, 2015 5:48 AM

May 14th, 2015 1:21am

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

Regards,

Free Windows Admin Tool Kit Click here and download it now

May 18th, 2015 5:15am

Hi,
I'm marking the reply as answer as there has been no update for a couple of days.
If you come back to find it doesn't work for you, please reply to us and unmark the answer.

Thanks

May 29th, 2015 1:50am

This topic is archived. No further replies will be accepted.