• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding the same field in the column field list of pivot table

N

NANKA

New Member
Hi,

I am showing the %row total of certain categories per week in the pivot table. The source is a query table. When i am clicking insert I create the pivot table by checking in a data model.My goal is to have the following view of the pivot as shown in the image 1 attached.

Fort that I tried dragging po field in the pivot column field list. the same data column into the pivot table values field - but instead of just showing me the total count per week it's also showing count of each category. Image 2 attached.

How can I just only add the total count of categories as a second field to get the view as in the first image?

Also - because i have posted this same question in other forums to be transparent I am including the links below. No one was able to help me yet. Really hope you can help me!

Below are the links where the same question is posted.
https://www.mrexcel.com/forum/excel...odel-into-pivot-column-field.html#post4781987

https://www.excelforum.com/excel-ge...ted-field-but-its-greyed-out.html#post4586974

http://www.msofficeforums.com/excel/34803-adding-same-field-column-field-list-pivot.html#post112517

https://social.msdn.microsoft.com/F...exceldev#986e75b5-0dee-4746-b7af-bb8eb7f5525d
http://www.excelbanter.com/showthread.php?p=1628546#post1628546

Your help is much appreciated!

Thanks!
nanka
Mod Edit: Thread title edited.
 

Attachments

  • image 1.PNG
    image 1.PNG
    12.9 KB · Views: 13
  • image 2.PNG
    image 2.PNG
    15.3 KB · Views: 12
Last edited by a moderator:
@NANKA
Do You want to get an answers as snapshot?
If You really would like to get any answer, You should upload real Excel-file!
With sample file it would be more possible to offer something!
 
Thanks @vletm.

Uploading the sample file now. In the first pivot table with the yellow highlighted column i am showing what i want to achieve. Basically i want to have that yellow highlighted field inside the pivot table.

In the second pivot table - I am showing that by dragging the same field second time is creating the counts for each individual category.

My question is - can i somehow not have them in the pivot table, but have the total count next to the % split column?

Nanka
 

Attachments

  • sample file.xlsx
    89.7 KB · Views: 6
1. They are both pivot tables! with the data source being the table in that same tab. What made you think they are not pivot tables??

2.That's the same layout i am getting just you changed the order of columns. My ask is to not have the count of each category but have the total count. Is that possible?
 
1. Those from Your file 'looks' like Pivot-tables in that file
... maybe You copied those somewhere?
Did You tested those Yourself?
>> If activate real Pivot-table then it is possible to edit like Pivot-table! <<
2. There are 'Totals' to see = 'Grand' ... Okay, new snapshot:
Screen Shot 2017-03-19 at 22.26.43.png
Those 'header' texts can modify as needed.
 
@NANKA
The most quickest way to get that 'Totals' to right side
is to make (this means really to make, not copy!) 2nd Pivot-table there ...
 
First: I sincerely don't understand what you mean by " those are not pivot tables". Are you looking in the right file?
First one is a pivot table - and next to it I just added a column NOT inside the pivot just the regular column next to the pivot to show what i want to achieve eventually.
Second pivot is a regular pivot table.

Can you please explain the steps how to get the total column of total count of categories in the same pivot table?
I don't need a second pivot table - i need a one pivot table with %s splits and the total count altogether in one pivot table - my first image.
 
Hi ,


Can you not hide the 3 columns resulting from introducing the PO field a second time ?

See the attached file.

Narayan
 

Attachments

  • sample file (10).xlsx
    93.4 KB · Views: 5
@NANKA - those are not Pivot-tables in Your Updated file:
as I wrote, have You tried to work with those? If not, then test and You'll notice.

As also I have sent files/photos ... if not want to do 2nd Pivot-table then
.. as again above ... hide columns which You don't want to see!
 
vletm said:
@NANKA - those are not Pivot-tables in Your Updated file:
as I wrote, have You tried to work with those? If not, then test and You'll notice.

As also I have sent files/photos ... if not want to do 2nd Pivot-table then
.. as again above ... hide columns which You don't want to see!
Click to expand...
Hi ,

Those are pivot tables , but they have been created using Excel 2013 or later version of Excel.

Narayan
 
@NARAYANK991
My Excel version cannot make Pivot-table as high row as in that file ... hmm?
Then activate 'Pivot-table'
then 'so far' it gives possibility to modify Pivot-table ... hmm?
... interesting ... changes ... means ...
Those don't work as Pivot-tables with me.
 
What possibilities? I didn't get the solution to my question - if you read my original post. Just hiding excel columns is not a possibility...
I need BOTH the PER CENT of each category and the grand total COUNT of all categories in the SAME pivot table.

None of the above does that.
 
Reply #4 - there are all values in same Pivot.
... why 'just hiding' Excel columns is not a possibility?
 
It's not a possibility because i want to create a pivot chart based on that pivot. I want to have a pivot table with the view outlined in my original post. Hiding a column is not the same as having a pivot table.
 
Yes reply #4 has all the values in the same pivot table - which i can also get. The whole idea here is to have all those values EXCEPT COUNT OF EACH CATEGORY BUT LEAVE THE COUNT OF TOTAL.
...
 
Sometimes You have to use 2nd best solution.
Hiding column would be as radical act as
no use all columns from Pivot with Chart.
If get more then,
it would be easier to use more
... opposite way ... hmm ... not possible!
So, good luck!
Try to find balance!
 
vletm said:
Sometimes You have to use 2nd best solution.
Hiding column would be as radical act as
no use all columns from Pivot with Chart.
If get more then,
it would be easier to use more
... opposite way ... hmm ... not possible!
So, good luck!
Try to find balance!
Click to expand...

@vletm
There is not a need to find a balance - the need here is to find the solution to this problem. Sorry - I am concerned you did not understand the question.
I am automating my report where the pivot table feeds the pivot charts which is refreshed weekly.
Seems like you don't have that level of advanced knowledge to help...so please don't waste my time with replies that are not helpful at all and are totally irrelevant...
 
You must log in or register to reply here.
Back
Top