Pivot Tables - Ratio from some Subtotal Data

[jagrok]

Dears

I have some database which I was importing from Ms Access. This database have some special columns.

I`m getting for excel sheet this data and creating my table pivot, some tables data, month, sum of cost A, sum of cost B

I was created some additional columns Subtotal A and Subtotal B which was counted from sum of cost A and B.
I would like to create additional columns still using Pivot Tables with option which can be able to count Ratio from SubtotalA and SubtotalB

Do you have a some ideas how to do it ?

[noboffinme]

Hi jagrok,

The 'Calculated Field' feature may help, can you post the file?

[jagrok]

Hi

I was thinking aboout the same flow as yours, but i cannot choose the SubtotalA and SubtotalB.
Of course i can chooose from month, sum of cost A (sales) and sum of cost B (service).

I`m new one which are trying to understand the process of Excel.
But i hope that my question are clearly enough to understand

About my files, its just simple file, which was specially created for this issue.
Normally my data have a lot off records ... which cant be calculated manually


Thank you for your support

[noboffinme]

Hi jagrok,

Have a look at the attached.

[newdoverman]

You don't say what format you are wanting Ratio to be. The format can be accomplished by applying a number format. This was done with a Calculated field. The formula is simple =SALES/SERVICE

This is what the result looks like:

	A	B	C	D	E	F	G
4	Row Labels	Suma z Sales	Suma z Service	Suma z Sales2	Suma z Service2	Ratio Fraction	Ratio %
5	1	1	33			1/33	3.03%
6	2	2	8			1/4	25.00%
7	3	3	7			3/7	42.86%
8	4	5	3345			1/669	0.15%
9	5	3	23			3/23	13.04%
10	6	6	4343			6/4343	0.14%

The calculated field dialogue is filled out like this:

Capture 2.JPG

[jagrok]

Ok maybe we was misunderstanding.
But i want to calculate ratio from Suma z Sales2 to Suma z Service2( this Column is a Field calculated with Subtotal )

[oeldere]

Please define subtotal, since you don't have duplicated month in your example.

Do you mean running total (jan/jan / jan/febr / jan/march)?

You add you have excel 2007 and you post an excel 2003 file.

Which one is correct.

This can be acchieved (excel 2007) on the second tab of the value(box) => running total.

[jagrok]

Please look at this sheet where im showing the problem:
simple.png

[jagrok]

Additional question:

Is this any option to hide some value of Chart and represent only one column in Pivot Table ?

[newdoverman]

There was something strange about the construction of the Pivot Table in that refreshing caused all the data to disappear so, I reconstructed the source data and added a couple of fields based on your original Pivot table and then created another Pivot Table with Calculated fields. One calculated field shows the ratio as a FRACTION and the other as a PERCENTAGE.

You can hide columns the usual way by selecting and right clicking and choose HIDE. There is also a filter on the Miesiac field that will allow you to pick what you want to show in the rows.

[jagrok]

Really i was look at this document, but i seeing only one pivot table.
In the Sheet/Tab number 5 there is a pivot table.
In the Sheet/Tab number 1 there is only a data, but this data is to much.
I have only A,B,C column

But i understanding you was creating 2 pivot tables ?

May u show process step by step ?

1. Getting data from Database - which is in Column A,B,C
2. Counting as i was showing on the earliest picture, using "calculated field" its will be called as sum of Column B and Sum of Column C
3. Getting Ratio Percentage from column "Calculated field" ColumnB/ColumnC - but i suppose you are counting this in additional Pivot Table

Becouse there are a lot of rows, please do as possible fully automated

At home i`m using office 2007 in office 2007-2010

[newdoverman]

I have only taken the data that you have produced in columns headed Sum of Suma z Sales2 and Sum of Suma z Service2 and added it to the original data and then created a new Pivot Table with the added data. (I don't know for sure how you created the columns with the running totals and they don't show up in the field listing to create a calculated field).

Here is your workbook with the Pivot Table that you created with the calculations to the right of it.

[jagrok]

Dear, Thank you for your support but,please back to my earliest posts where i showing the screen.

I had already calculated this rows with the same method as you are showing in the latest file.

My question was:

How to include this calculation to have this column in Pivot Table.
Which this will give you chance to include the graph also in Pivot Chart and generate it dynamically using filters.

Ok i can imagine that the problem is with my description.

[newdoverman]

How to include this calculation to have this column in Pivot Table.
Which this will give you chance to include the graph also in Pivot Chart and generate it dynamically using filters.

Ok i can imagine that the problem is with my description.

Some things you can't do directly in Pivot Tables and I think that what you want is one of them. That is why I added your data to your original source data. That allowed the Running Totals that you have calculated to become a real part of the Pivot Table as fields. If you look at your Pivot Table with the running totals, the running total fields do not show in the list of fields in the Pivot Table Field List. You want to have them show there so that you can use them in Calculated Fields.

When I added your Running Total data to your original source data for the Pivot Table, those items then became fields that could be used to create Calculated fields. Without doing this, it wasn't possible.

The file included in Message 10 has the data added to the source data and the Pivot table shows the added fields in the Field Lists so that they can be used in Calculated fields. I made two Calculated fields so that you had a choice of what you wanted.

I hope that this helps.

PS
I had to save the file as xlsx format because for some reason the original file format was refused by my computers.

[jagrok]

Ok.

I was goes to the other ways.

I was created additional pivot table using some information from pivot table.
Then only i need to refresh both pivot tables, after the update.

So maybe i will just place another question.

If i`m generating some charts using this pivot table, can I hide some/chosen information in Chart ?
Of course the condition is: don't delete data from pivot table.
Because it will cause loosing some calculation.

[newdoverman]

Use the Pivot Chart Wizard, select the data and follow the prompts. You will be presented with the same thing that you saw when you created your Pivot Table with the addition of an empty chart. You choose the chart type and when you insert the fields, the chart will be created. You can include/exclude what you want just like you did with the Pivot Table.

[jagrok]

In 2007 this option is not available as Chart Wizard, but it`s mean that i need to create 3rd pivot table ...

[newdoverman]

You can create a chart by selecting the data in a Pivot Table and then create the chart. The way that the Pivot Table displays the data is the way that the chart is created.

[jagrok]

Ok.

Thanks a lot for your suggestion.
I was thinking that the Excel give me more possibility