Pivot Table - Calculate Difference Between Two Calculated Fields

Mar 29, 2012

I have a pivot table listing different company names in the first column under 'row labels' and there are calculated fields, a count and an average in columns B and C respectively. Is it possible to insert another field in column D that calculates the difference between values in column B and C (even though they are calculated fields themselves)?

View 3 Replies


ADVERTISEMENT

Adding Calculated Fields To Pivot Table

Jun 7, 2013

I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.

Day
Count of Posts
Sum of Impressions
Impressions Per Post

Monday
52
1,881,468

[Code] ........

View 1 Replies View Related

Using Pivot Table Summary Fields In Calculated Field?

Jan 21, 2012

I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:

I have a collection of customer purchase records, which look like the following:

Customer Name, Customer ID, Purchase Amount, Activation Date
-------------------------------------------------------------
John , 100 , $150 , 2011-04-01 17:07:50.0
John , 100 , $250 , 2011-04-01 17:07:50.0
Paul , 101 , $125 , 2011-08-20 11:10:27.0

I have several 1000 records like the above and I need to create a summary report which looks like:

Customer Name, Total purchased, Activation date, Avg monthly purchase
-----------------------------------------------------------
John , $350 , 2011-04-01 , $175

The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.

In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:

1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"

2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.

View 2 Replies View Related

Using References In Pivot Table Calculated Fields Formula

May 23, 2007

Worksheet A contains two columns, that maps individual operations to their unitary cost:
COLUMN 1 = a list of operations
COLUMN 2 = the cost associated with each operation.

I can do a vlookup on this worksheet to retrieve the cost of each individual operation.

On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.

operation 1 was performed 5 times
operation 2 was performed 7 times
operation 3 was performed 4 times

I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be
= operation * vlookup("name of operation","range for lookup table",2,0)

... unfortunately, "references, names and arrays are not supported in pivottable formulas".

View 9 Replies View Related

How To Create Calculated Field Based Off Two Avg Fields In Pivot Table

Feb 12, 2014

I have a Pivot table that pulls the Avg of two fields for two months, see example below.

Avg Gross $ Avg Net $
Jan 2014 20 10
Feb 2014 30 20

sample 1.png
See sample attached.

The Avg Gross and Net is shown by going into the values and selecting "Summarized value by -> Average".

On the right side of this Pivot, what I wanted to do is to show a Avg Gross to Net $ in this pivot. So the formula should take "Avg Gross $" - "Avg Net $" = Avg Gross to Net $.

I am having trouble calculting this new field in the pivot table using a calculated field because the Calculated field pulls the variables from the existing field list and there isn't a field called "Avg Gross/Net"....I need to find a way to calculate the Avg Gross to Net into the Pivot table so I can pull a pivot graph out of it.

View 3 Replies View Related

Create Pivot Table That Has Calculated Field For The Difference In Amount

Feb 27, 2013

I have 3 fields:

Person, FinancialYear, AmountUSD
Pete, FY12, 8000
Pete, FY13, 7000
Jeff, FY12, 5000
Jeff, FY13, 5500
Sam, FY12, 4000
Sam, FY13, 3500

How to create a pivot table that has a calculated field for the difference in AmountUSD between FY12 and FY13 (for each person).

View 1 Replies View Related

Calculate The Difference Between 2 Columns In Pivot Table?

Aug 14, 2014

So I am running in to a large brick wall! I have a single pivot tablet with a column for items received and then a column for items shipped. I would like to create a calculated field in the pivot table that will subtract the items received from the items shipped columns. I have attempted to add the calculated field using the tool bar but it keeps giving me "0" for every item on the pivot table when there are obvious differences.

View 1 Replies View Related

Calculate The Difference Between Two Columns In A Pivot Table

Aug 26, 2009

Is a way to calculate the difference between two columns in a Pivot table .

I have the data something like this in a pivot table....

Sum of MetricValueColumn LabelsRow LabelsFebruaryMarchAprilMayJuneJulyNet New My SitesNet New Team Sites0Number of Provisioned Users322059319906322372350239332914328404Total Capacity In Use (GB)1585.381722.761824.731947.772124.282262.78Total No. of My Sites208212316924682307313313134317Total No. of Team Sites101751082311396683273227940

What i need to do is to calculate the "Net New My Sites" and "Net New Team Sites" values something like this....
Net New My Sites (March) = Total No. of My Sites(March) - Total No. of My Sites(February)
Net New My Sites (April) = Total No. of My Sites(April) - Total No. of My Sites(March)

View 9 Replies View Related

Pivot Table Fields To Calculate Delta Between Two Proceeding Columns

May 5, 2014

I have a Pivot Table. I have two Columns Currently Current Week and Previous Week. The Values are displayed as a count for the Status Field. I would like to know if it is possible to add a calculated formula that will take the count difference between Current Week and Previous Week?

View 1 Replies View Related

Calculated Fields In Pivot Tables

Dec 5, 2006

My Pivot Table data section contains calculated fields and normal fields...both have sub and grand totals. The calculated items are correct but. the calculated field totals are incorrect. The normal field totals are correct. The calculated field totals are ... the True total multiplied by the number of items!

View 2 Replies View Related

Using Calculated Fields In Pivot Instead Of Changing Raw Data First?

Dec 31, 2013

I created a nice excel file with some cohort analysis regarding the number of months customers of different cohorts stay with a company. Basically I do a little manipulation on the raw sign-up data sheet and then run a pivot on it.

I want to see the cohorts in two ways:
1. number of customers.
2. $ from customers.

I have the first 1 and I want to get the second one without going through the same procedure again (i.e. manipulating the raw data). In order to get the $ terms, all I need is to multiply a $ column in each row in the columns that indicate the number of months that passed since a customer joined (see the attached example file please). When I try to do it with calculated fields in the pivot table, Excel multiplies the total number of customers with total $ from them all put together, which is of course wrong.

Any way to achieve what I want without having to created another data sheet just for $ terms?

I believe the attached excel file: dummy_for_cohort.xlsx

View 6 Replies View Related

VBA - Adjust Pivot Table Included Fields To Match Another Pivot Table

Mar 14, 2013

I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.

Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.

I imagine it would be something in the style of:

"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"

View 1 Replies View Related

Calculated Field In Pivot Table?

Jan 21, 2014

Can I add a calculated field formulas in Excel pivot table. Such as CONCATENATE?

View 2 Replies View Related

Pivot Table Calculated Field

Mar 26, 2014

Ihave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.

View 14 Replies View Related

Calculated Item In Pivot Table

Mar 4, 2014

I'm trying to insert a calculated row in a pivot table. I keep getting the error message 'Pivot table items can only refer to items in the same field as the calculated item'

Below is the format of my data:

Region(All)
Sub Division(All)
Sub Cluster(All)
Cluster(All)
A/C Level 3(All)
A/C Level 1(All)
A/C Level 4(All)
A/C Level 5(All)
Division(All)

Values
A/C Level 2Sum of FY11Sum of FY12Sum of FY13
Net Revenues 100 200 300
Op Exp 50 40 30
Calculated Field50 160 270

View 5 Replies View Related

Add Calculated Item To Pivot Table

Jun 21, 2009

Attached is a worksheet example, of my beauty salon with 5 therapists.

The input sheet - is the shhet into which I input data on a daily basis.

Weekly inc - This sheet shows the total that each therapist made in apivot table based on the input. The week numbers are 1 to 52 (or 53) depending on each financial year (07/08, 08/09 etc).

What I want to do is either have another pivot table or to add an calculated item to the weekly inc pivot table showing me how much commission each therapist took each week? This is to be done automatically by excel.

This commission is calcluated by using the target comm figures - E.G. Leanne's target is 480 - so she has to earn 480 a week before getting a commission. So if in a week she earns 500 - she will get a commission on 20 (500 - 480). The commission is 10%, so she will earn 2 that week.

View 14 Replies View Related

Calculated Item In Pivot Table?

Feb 18, 2012

I'd really like to include the yellow fields below inside the Pivot Table but I can't find a way.

On the collumn Label I have the Category, which can be Budget or Forecast. Then on the Values Field, I sum the Revenues.

RevenuesCategoryVar GrossBudgetForecastK €%Total285.264142.548-142.715-50%

I want to calculate the difference between Budget and Forecast, in € and %. I was trying to use a calculated Item but whenever I place the cursor on the Budget, Forecast or Category cells and try "Calculated Item", I get an error message saying that the Item cannot be modified..

View 1 Replies View Related

Pivot Table Calculated Field?

Jun 19, 2012

I have a pivot table with 2 row filters (dept and name) and then three columns - Year 1, Year 2. I need to add a calculated field inbetween Year 1 and 2 to show the variance in dollars from ( Year1-Year2) as well as an additonal a column that shows the percentage change between the tw o years . I know I can copy this over and add formuals in Excel but I need to retain the features of the pivot tables due to the 2 row filters?

View 1 Replies View Related

Pivot Table Calculated Formula / Sum Of Count

Aug 13, 2014

Is it possible to create a Sum of Count Calculation on a Pivot Table?

View 6 Replies View Related

Pivot Table - Calculated Field / Item?

Mar 18, 2014

I have a data that consists of months, forecast and actual.

I've created a pivot table where; months is on the row, forecast and actual is on the values

I wanted to know the percentages between forecast and actual (forecast / actual) to see how it perform each month.

How do I go about inserting a pre-defined named called "percentages" on pivot table with calculated field or calculated item on pivot table itself?

View 3 Replies View Related

Pivot Table - Add Calculated Field Not Working

Apr 26, 2012

I have and excel sheet that pulls data from an external source (ServiceNow) via MS Query.

From the exported data, I have the following:

Date opened (MM/DD/YYYY HH:MM:SS AM/PM)
Date closed (MM/DD/YYYY HH:MM:SS AM/PM)
calendar_stc (this exports in seconds, such as 136) (this equals date closed minus date opened for each record)
assignment group

From this export, I'm trying to make a pivot table to show the average calendar_stc per assignment group, per month, per year; but in hours, not seconds.

I thought this was an easy process, create pivot to group row data by years then by months. Then add in assignment group to to the Rows as well. From there, I added to the values section of the pivot the calendar_stc. The default, of course, is to sum each row. But I need the average so I switched it to average. Now it is showing the correct average calendar_stc in seconds per group. To make it hours, I figured I could add in a calculated field. To do that I added the formula of =calendar_stc/60/60 for the calculated field. When I hit okay, it's giving me the SUM in hours for each row. Modifying the field settings to average does not change the numbers. So I went back into the formula and modified =average(calendar_stc/60/60). And that is not working either.

Here is an example to show the math with my formula.

View 3 Replies View Related

Removing Calculated Field From Pivot Table Using VBA

Feb 5, 2014

I am trying to create a dynamic pivote table that can be update using a value in a cell. I can get everything updating and working correctly except I am unable to remove the one calculated feild from my pivot table. I am able to remove all other fields without problem. Code below: (lines 13 and 14 is the code that i cant get to work)

Private Sub CommandButton1_Click()
Dim dt As String
Dim dt2 As String
On Error Resume Next
dt = Sheets("Executive Summery").Range("M1")
dt2 = dt & "2"

[Code]....

View 5 Replies View Related

Pivot Table Calculated Field With Count

Jun 26, 2014

I am trying to calculate a ratio for Hours per Ticket by month. I have a pivot table that COUNTS tickets and SUMS hours per month. I was hoping to create a calculated field to the jist of COUNT(Tickets) / SUM(Hours), but this does not work because calculated fields aggregate data.... I was hoping to keep it all in a pivot table so that I can still use slicers.

Pivot Table:

Rows: Years, Months
Values: Tickets (Count), Hours (Sum)
Month
Tickets (count)
Hours(sum)
Throughput (Calculated)

2013

Apr
65
22
2.97

[Code] ..........

View 1 Replies View Related

Pivot Table Calculated Item Removal

May 24, 2007

I have cut down my problem to its simplest form - in the real world my spreadsheet is significantly more complex but the attached spreadsheet demonstrates it nicely!

The data is straightforward. PT1 shows the data without a calculated item whilst PT2 is the same PT but with the addition of a calculated item for GP (i.e Sales - COS). Unfortunately adding this has resulting in the PT growing and adding "ilogical" combinations of data that didn't exist in the source data.

In my current project I have "cheated" by using a helper column and filtering on this using the Worksheet_PivotTableUpdate event to hide the unwanted rows but surely there must be a way of removing them "properly".

Another problem, and I guess this is just the way it is, is that my PT takes ages to calculate presumably becuase it is calculating this formula for every combination of values?

View 9 Replies View Related

Pivot Table Calculated Field Based On 2 Columns?

Jun 9, 2014

I have a Table with 2 columns "Due Date" and "Completed Date". The pivot table from the table gives the count of each column. I want to have a calculated column giving the % complete, ie. "Count of Completed"/"Completed Date"*100. When I attempt to create this formula (using Pivot Table Options > Fields Items and Sets > Calculated field), I get a DivZero error, even though both columns are not zero. How do I create such a calculated column?

View 3 Replies View Related

Totals And Subtotals Of A Calculated Item In A Pivot Table?

Mar 17, 2014

I have an issue with a calculated item in my pivot table, because in the totals and subtotal it shows the sum of the column but I want to show the formula that I've specified to the calculated item.

For example, in the attached file, I have in rows the field "name" that has four values (A,B,C,D), in columns the field "Groups" that has "G1" and "G2". I add in the rows the calculated item "G1/G2" that has the formula G1/G2. The problem is that in the total the column "G1/G2" doesn't show the division of the total of "G1" and "G2".

View 3 Replies View Related

Pivot Table, Calculated Item.... No Data, Still Creates A Row

Oct 12, 2007

I have a Pivot table.

Layout:
Rows: City, Restaurant, Cashflow
Column: Date (Month/Year)
DataField: $Amount

"City" might be 7 cities
"Restaurant" may be 32 restaurants, some in some cities; but not other
"CashFlow" can be either "Revenue" or "Expense"

The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.

Now.... the trouble...

I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries.

How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?

Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?

View 11 Replies View Related

Excel 2007 :: Pivot Table Calculated Field

Mar 19, 2009

I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.

I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).

1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance

2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance

This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?

View 9 Replies View Related

Pivot Table - Incorporate Calculated Field For Sub Group

Sep 6, 2012

How to incorporate a calculated field for a sub group.

Here's what the data in the table looks like.

Competitive

Managed

ID
A
B
C
A
B
C

[Code] .....

I am looking to get 3 calculated fields onto the Right hand side that would essentially be the following Formula
=IF(L4"",IFERROR(D4/H4,"n/a"),"")

Thus,
1st Column
=IF(Competitive(A)"",IFERROR(Competitive(A)/Managed(A),"n/a"),"")

2nd Column
=IF(Competitive(B)"",IFERROR(Competitive(B)/Managed(B),"n/a"),"")

3rd Column
=IF(Competitive(C)"",IFERROR(Competitive(C)/Managed(C),"n/a"),"")

The other option would be how to set up conditional formatting to always take on the appearance of Column K of the pivot table. Thus, this calculated section would always appear as though a part of the pivot table and would adjust based on any pivot table items being expanded and collapsed.

View 7 Replies View Related

Calculated Field Average Error In Pivot Table?

Dec 10, 2013

I created a custom formula for a pivot table.the existing columns are weekly averages.I made a formula to calculate the average of 5 individual weeks.But when one of the value is blank.Blank cell is considered as zero.and hence the final average is wrong.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved