Adding formula to Pivot Table when calculated field not available

Ucubia

Board Regular
Joined
Mar 17, 2010
Messages
88
Hi

Apologies for the basic nature of the following question but hoping someone can help.

I have a spreadsheet that contains hundreds of servers each with a unique hostname, also have a list of applications and an alignment to indicate which server(s) have which application(s) - noting that a server could serve multiple applications or could be a single server maintaining a single application. I have crossed referenced each application to indicate the status of the application whether giving an "Active" or "Inactive" state.

I have created a Pivot table whereby:

Columns = State (Active or Inactive)
Rows = Device (name of the server)
Values = ApplicationID (this being a distinct count)

The pivot table results in the Device being identified for the number of applications attributed to it - that is fine. For example, the Pivot results will look something akin to:

Device Active Inactive GrandTotal
Fred 1 0 1
Fredda 0 2 2
Freddie 1 2 3

What I want to do, is add a simple column to the Pivot Table, that performs a calculation of "Active - Inactive" and essentially if a negative value is returned then this will mean the server is not being used. I have looked at Calculated Fields but selecting the pivot table doesn't give me the option, only to create sets.

Ideal would be to remove the "GrandTotal" and replace with a formula to perform the calculation of "Active - Inactive"

Any help, very gratefully received.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
If the calculated field option is greyed out, it means you loaded the data into the data model (presumably to get the distinct count option), so you need to use power pivot to perform the calculation. You can do that either by creating new calculated columns in the model for active and inactive (the equivalent of using calculated items in a regular pivot table) or by creating measures, which are the equivalent of calculated fields.
 
Upvote 0
If the calculated field option is greyed out, it means you loaded the data into the data model (presumably to get the distinct count option), so you need to use power pivot to perform the calculation. You can do that either by creating new calculated columns in the model for active and inactive (the equivalent of using calculated items in a regular pivot table) or by creating measures, which are the equivalent of calculated fields.
Thank you Rory for the prompt response and yes I needed to get the distinct count - will now read up on the Power Pivot approach as I am not a big user of it.
 
Upvote 0
Not sure the Power Pivot is going to work - the State field will either contain "Active" or "Inactive" and a server, using the previous example of Freddie for the Pivot Table, there are 3 applications associated to, for example in the spreadsheet itself:

Freddie App1 Active
Freddie App2 Inactive
Freddie App3 Inactive

So Pivot Table will return :
State
Device Active Inactive Grand Total
Freddie 1 2 3

So not sure how the calculation could be done within Power Pivot from what I can see as it would the sum(State-State) which of course is erroneous - or is there a way to reference sum(State.Active - State.Inactive) ??
 
Upvote 0
You'd either create additional columns - one for Active, one for Inactive - or create a measure for each using something like =CALCULATE(DISTINCTCOUNT([ID]),Table1[Status]="Active") and you can then calculate the difference using another measure that just subtracts the second measure from the first.
 
Upvote 0
You'd either create additional columns - one for Active, one for Inactive - or create a measure for each using something like =CALCULATE(DISTINCTCOUNT([ID]),Table1[Status]="Active") and you can then calculate the difference using another measure that just subtracts the second measure from the first.
Thank you again for your assistance Rory, I failed miserably but managed to get data rebuilt with an additional column and that worked. Many thanks again
 
Upvote 0

Forum statistics

Threads
1,222,396
Messages
6,165,761
Members
451,985
Latest member
jchunowitz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top