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
ADVERTISEMENT
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
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
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
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
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
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
Jun 21, 2007
I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer.
clientcode Amount countries
a1 1.000,00 kenia
a2 2.000,00 kenia
b3 1.000,00 kenia
b4 3.000,00 kenia
b5 2.000,00 kenia
c5 1.000,00 senegal
c6 3.500,00 senegal
c7 4.000,00 senegal
c8 5.000,00 senegal
Lets say I have a list like this and I want to count the number of clients (3) or countries(2).
I can only get the total of rows per client but not the subtotal 3 for the number of clients.
a - 2
b 3
c - 4
View 9 Replies
View Related
Jul 24, 2007
I can't find a way to display "just" subtotals which are > 500k ..
View 9 Replies
View Related
Aug 27, 2007
With the following data Excel's pivottable's subtotal shows underlying MAX total instead of subtotals relating to values displayed.
SalesRepOrderNoItemValue
ACON12334
ACON12322
ACON12412
ACON12424
ACON1248
Pivottable:
Sum of ItemValue
SalesRepOrderNoTotal
ACON12356
AC ON12444
AC Max34
Rather than use SQL (I don't have data on SQL server) or re-organising data by order number, is there a fix using calc items, calc fields or grouping to show correct MAX figures? Why Excel doesn't simply evaluate visible cells seems crazy to me!
View 9 Replies
View Related
Dec 21, 2009
I've just started using Pivot Tables and have a problem right away. Couldn't find anything with the search function, but am not really sure, what to search for, either
Part of my data looks like this
NameLot #BinAmountABC123CB18C174.025 ABC123CB18C-24.975 ABC123CB18C-24.975 ABC123CB18C12.616 ABC123CB18C-12.616 ABC123CB18C-24.975 ABC123CB18C-24.100 ABC123CB18C-25.000 ABC123CB18C-25.000 ABC123CB18C-25.000
Now this is a material that is stored on a pallet with the code CB18C. The total sum of material that is added to/taken from this pallet adds up to Zero. My Pivot Table, however, believes the total is something like 2.84217E-14.
Sum of Amount NameLot #BinTotalABC123CB18C2.84217E-14 123 Total2.84217E-14ABC Total 2.84217E-14Grand Total 2.84217E-14
This problem occurs several times, the raw data adds up to Zero, but the Pivot Table calculates these tiny, tiny totals. Is this a known bug or can Pivots not handle 3 decimal data?
View 11 Replies
View Related
Jan 18, 2007
When I have two or more fields on the left of the pivot layout, sometimes I only want the line item data and the grand totals, but not the various subtotals.
Is there a way to suppress the various subtotals created by a pivot table?
View 9 Replies
View Related
Jun 6, 2007
Pivot tables. They have a habit of adding automatic subtotals to each field of data you drop into the pivot. Is there any way of setting the default to "none" rather than "automatic". It's driving me insane.
I am toying with the idea of writing some code to eradicate this issue, but if there's something native to Excel which will do the trick, that would be much more efficient.
View 7 Replies
View Related
Oct 13, 2009
attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?
I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.
View 5 Replies
View Related
Jul 18, 2007
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.
View 2 Replies
View Related
Oct 29, 2012
I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.
View 1 Replies
View Related
Jun 30, 2006
I have a pivot table with 10 sub total rows, I would like to know if it is possibe to change the font into bold for rows who are subtotals.
View 6 Replies
View Related
Jan 21, 2014
Can I add a calculated field formulas in Excel pivot table. Such as CONCATENATE?
View 2 Replies
View Related
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
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
Aug 13, 2014
Is it possible to create a Sum of Count Calculation on a Pivot Table?
View 6 Replies
View Related
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
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
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
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
Mar 26, 2012
I've gone into the table options and ticked Show totals for Rows and Columns but as you can see it only shows a total for column????? I've manually done a sum but that means it won;t be included on my pivot graph? [Code]....
View 8 Replies
View Related
Sep 28, 2007
I am using a database sheet to log pupil misdemeanors, namely Lates, Disruption and Absconding. From this database a pivot table is generated from which a 'hit list' of the worst 10 latecomers are compiled. The code that I have at the moment (see below) sums all misdemeanor activity by pupils rather that just values relating to lateness.
I have included a sample file with expected output ....
View 6 Replies
View Related
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
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
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
