Adding formula to Pivot Table when calculated field not available

[Ucubia]

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.

 

[RoryA]

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.

 

[Ucubia]

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.

 

[Ucubia]

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

 

[RoryA]

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.

 

[Ucubia]

ok thank you Rory, the measure looks to be the easiest option - i will let you know

 

[Ucubia]

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