Free Microsoft Excel 2013 Quick Reference

Percentage Difference in Pivot table

I explained this terribly the first time around, so here is take 2.

I have the below data within my pivot table, yet i want to have a percentage difference column in place of the grand total column, any ideas?

Client20072008Grand Total2D Design Ltd14.6838.9753.65

Thanks


Post your answer or comment

comments powered by Disqus
Hi,

I have a pivot table with Sales and Budget, if i need to find the difference in pivot table i think i need to use field settings, however i am not getting the result

I have attached the same file for reference

thanks for help

Arvind

I have a pivot table that displays different summary volumes of the different types of lumber that has been cut in one day. What I need is to have the pivot table calculate the percentage of one product of the total. Here is some sample data. I would also like those percentages to refresh when the pivot table is refreshed.

Specie SM Grade SM Grade Product SM Grade Width Data Total
SP COM 4/4 04" Sum of SM Grade Volume 1327
Sum of SM Grade Pieces 342
06" Sum of SM Grade Volume 1998
Sum of SM Grade Pieces 392
08" Sum of SM Grade Volume 10248
Sum of SM Grade Pieces 1189
10" Sum of SM Grade Volume 8413
Sum of SM Grade Pieces 736
12" Sum of SM Grade Volume 39332
Sum of SM Grade Pieces 2697
4/4 Sum of SM Grade Volume 61318
4/4 Sum of SM Grade Pieces 5356
SP Sum of SM Grade Volume 212834
SP Sum of SM Grade Pieces 15826

Any help would be greatly appreciated.

Thank you,
Liz

Hi

A quick questions about pivot tables:

I have two data fields. One vith values from last year, and one with values
for the current year. Both are shown as % af column. Now, I would alså like
to show the difference in percentage points. Is it possible to make a
calculated field that subtract one value from another and show the difference
in percentage point?

Can anyone give me a hint here?

Thanks.

/Sune

I have 2 columns in a Pivot Table. One called Sum of AD Total, the other called Sum of Total Cost.

I want to add a third field that displays the Sum of AD Total as a percentage of the Sum of Total Cost.

So I assumed I would use the "% Of" facility in the "Show data as" drop down, and "Total Cost" in the Base Field drop down. But what goes in the Base item field as whenever I try and put something in I get N/A returned as a result in my PT?

I'm attaching a very simplified version. I'm sure I'm doing something very simple wrong, so any help appreciated.

Thanks

Lee

I have a pivot table with revenue for 2007 and 2008 and want to have a column calculating the difference. I know i can have it outside the pivot table but of course when i adjust the pivot table, it will be messed up.

Any ideas as i have hit a dead end

Thanks in advance

Hey,

I've been looking through some of the forums, but i just can't figure out how to do this.

I'm trying to find percentages of subtotals in pivot tables (preferrably in a new field (within the pivot table itself), so it can auto-update itself).

Right now, my pivot table's top most row field is "Accounts", which then breaks down by "Product Type","Category", "Sub Product Type", and "Name".

So what I"m trying to do is break down the "Category" in each "Product type"
and then further break down each "Sub Product type" in each "Category".

Is this possible? Thanks for all the help.

Hey everyone,

I have a list of, say, the number of vegetables (potatoes, turnips, carrots etc...) sold at my grocery store every day.

I'm trying to use a pivot table to summarize the daily difference for each vegetable. Right now I have to create columns for each vegetable and calculate the daily difference, and then import all of this in to a pivot table. This takes a while and doesn't really work well...

Is there any way to look at the difference in a pivot table without creating a new column in the original table? I tried using calculated fields but they would only let me check the difference between different vegetables and not one vegetable against itself?

Many thanks,
AR

Dear Friends,

I have a pivot table like this:

		QUALITY POINTS								
ITEM	COMPANY	A1	A2	B1	B2	C1	C2	D1	D2	TOTAL
HANDYCAM	A	12	4	2	2		2			22
	B	3	5	9	4	3	9	3	1	37
	C	11	3	4	2		1			21
	D	13	9	2	4	1	2	3	5	39
I need to insert a row alternatively to do a percentage calculation of each quality points, as like below:
		QUALITY POINTS								
ITEM	COMPANY	A1	A2	B1	B2	C1	C2	D1	D2	TOTAL
HANDYCAM	A	12	4	2	2	0	2	0	0	22
		54.55	18.18	9.09	9.09	0.00	9.09	0.00	0.00	100.00
	B	3	5	9	4	3	9	3	1	37
		8.11	13.51	24.32	10.81	8.11	24.32	8.11	2.70	100.00
	C	11	3	4	2	0	1	0	0	21
		52.38	14.29	19.05	9.52	0.00	4.76	0.00	0.00	100.00
	D	13	9	2	4	1	2	3	5	39
		33.33	23.08	5.13	10.26	2.56	5.13	7.69	12.82	100.00
Is it possible to do like this in pivot table? If NO, any alternate method to accomplish this target? The items / company columns in my table frequently varies. Hence I am unable to refer the pivot table cells in any other sheet.

Any help is highly appreciated.

Thanks in advance.

acsishere.

Dear All,

Please help me to take the sale profit/loss report in percentage in pivot table now iam using manually,
Please find the attached file.

Thanking you in advance
Mubeen

Hi,
I have two questions about dealing with formulas in pivot tables and how to make them dynamic. First let me give you some further background.

My database countains the following variables: Country, Company, Period, Product, Category and Sales. Out of this database i created a pivottable which sums the total sales amount for each company in every period. Further selection on country and category is possible in this pivot table. See my attached file!

I want to express the sales of each company as a percentage of the total sales in that period. For example for company A in period 1 their total sales was (929/3172) 29,3% of the total market sales. Which formula do i have to use in my pivot table to express company's market shares?

Further my pivot table can be specified more detailed by country and category. I want to be able to select on country and category in such a way that the company sales are still expressed as a percentage of the market sales. In other words how can i make my pivot table dynamic?

I hope i explained my situation well. If not, don't hesitate to ask me. Thanks again!

Hi,

I have large number of data and I would like to caculate weighted average of two arrays. I can do a sumproduct but I would like to do this on more than one conditions.

For example, I would like to calculate the weighted average of all data for a particular day of week (MON or TUE) for all the 12 months. Is this sort of calculations easily done in Pivot Table scenario? Or Is it easier to do this with multiple (I mean sort of nested) sumproduct caclualtion?

I have data for every day of the year and would like to manipulate the data. I am doing this for various conditions based on the date and different functions, WEEKNUM, DAY of WEEk, etc.

Or is there any other way this can be done more efficiently.

In the example attached, I would like to find the value of SAT for month # 2, for example. Which is the easiest / most efficient method to do this type of caculations?

Thank You very much.

Chandra

I am having issue with subtotaling few calculated fields in pivot table. We generate report from ERP system that provides following items in Excel:

Customer Name Invoice Number Invoice Date(when the invoice was created) Sales Order Number Aging Date ( date when the report is exported to Excel) Invoice Amount (fnamount) Days Late ( Aging date invoice date)
My report attempts to find invoices amounts that are due between 0 days to 30 days past, 31 days to 60 days, 61 days to 90 days and 90 days and above. I use if formula to properly categorize my due payments into correct column.

Here is the example of my If formula:
0 to 30 = IF('Days Late'30,'Days Late'

Hello,

Is it possible to do % in pivot tables. I thought the calculated field was a way to get around % in pivot tables... For example, if i have the following pivot table:

Inv$ Total Sell-Thru Region DC
63,347 17,421,341 XXXX XXXX

1,809,608 99,536,632 XXXXX XXXXX

My goal is to get a percentage of Inv$ of Total Sell-Thru, therefore, i should see the following results: 0.004% & 0.02%

Any way to get the results? Again, i thought the caculated field would get me there....

Thanks,
Michael

I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.

so i have worksheet with a lot of data which are not very much in order so i need to change text manually which is very hard and almost imposible to do it withou mistakes!
simple example just to show what i need:
in row A have croatia,slovenia,bosnia,hungary,italy,germany,austria.... and more other countryes.
in row B have more different data - months in year - january, february, march...
under those months have monday, thursday, wednesday etc.
So sometimes i need to change italy/february/thursday into italy/february/morning
It's a larger data and more complicated than in this example so it's really difficult and hard to do it all manually or by filtering it to look where this criteria is and to input it manually ...

So by using pivot table it looks perfectly and everything is automatically organized as it should be. Problem is that i can't change text/values in pivot table. I would like to change cells which are showed in pivot table because it is well organized and i see it easy what to change(retype), but can't do it in pivot table in order to change it also in cells. What is easiest way to solve this problem?

Ivica

Hi there

I need to get the % of totals in the pivot table and don't know the formula
to use when using the Insert calculated field option in the pivot table.

Can someone assist

I understand how to use the custom calculations in the field settings in
pivot tables to derive a simple annual growth rate, using % Difference From,
(previous), and the year field .

Does anyone know a similar way to generate compound annual growth rates
(CAGRs) in a pivot table for each year in the series from the initial base
year?

I have tried doing a calculated field, but then the column total reflects a
sum of the CAGRs and not the CAGR for the total.

Is it possible to have a column in Pivot table with values of this
column derived from existing columns (like sum,product of values from
two or more columns)?

For example, I have column 1,2,3 and 1000 rows. Now, when I create a
pivot table I list different values from column 1 as rows in pivot
table and values from column 2 as my columns in pivot table. I get 10
rows (from column 1) and 4 columns (from column 2) in pivot table. Now
I want to create third column in pivot table by adding values from
first two columns of pivot table.

Is there any other way to do this without pivot table? I like the fact
that in pivot table I can double click on a cell and get all the data
behind it in another worksheet.

Jay

--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=510022

Hi,

I have a list with time entries

Date Hours worked Month
2008-10-01 4
2008-10-15 6
2008-11-01 3

I would like to add a column to the right with month in text such as oct,
nov in the example above. I have done that with formatting the date column
with "mmm", but when using that data as column data in a pivot-table, oct
appears twice (which is not surprising since it is actually different dates
in the cells even though it only shows month). So, what I think I need is
oct, nov and so on in text format in my month column so that it works in
pivot-tables.

How do I do that?

Thanks in advance,
Peter S

When I add a calculated item in a pivot table ie a "variance" column to
calculate "Actual-Budget", a lot of lines pop up with empty values, I'm just
wondering if there's a way to get rid of the lines (in my case it's the
different accounts) that has zero values. I know I can pick and choose the
lines I want from the drop down list, but is there a functionality in pivot
table that can get rid of the empty value lines automatically?

Thanks...

I have a percentage calculated with A divided by B, as an example, in data sheet and displayed in pivot table, for several weeks.
When choosing total (all weeks) in pivot all percentage numbers are simply summed upp. I want to see an average percentage for all weeks, using all A divided by all B.

How should I do this?
Thanks in advance!

Can anyone please tell me if it is possible to have conditional formatting in pivot table? For example. My data contains three columns. I want items greater than x number to be highligted in a different color. I know I can do this in the spreadsheet but how can I view this in the reflecting Pivot table. Even if I refresh the pivot table, it does not show up. I would really appreciate any help on this !! I am using Excel 2003 Thanks much !

Is it possible to have a column in Pivot table with values of this column derived from existing columns (like sum,product of values from two or more columns)?

For example, I have column 1,2,3 and 1000 rows. Now, when I create a pivot table I list different values from column 1 as rows in pivot table and values from column 2 as my columns in pivot table. I get 10 rows (from column 1) and 4 columns (from column 2) in pivot table. Now I want to create third column in pivot table by adding values from first two columns of pivot table.

Is there any other way to do this without pivot table? I like the fact that in pivot table I can double click on a cell and get all the data behind it in another worksheet.

Jay

I understand how to use the custom calculations in the field settings in pivot tables to derive a simple annual growth rate, using % Difference From, (previous), and the year field .

Does anyone know a similar way to generate compound annual growth rates (CAGRs) in a pivot table for each year in the series from the initial base year?


No luck finding an answer? You could always try Google.