Pivot Table: Adding the same column from the data model into the pivot column field

N

nanka

New member
Joined
Sep 19, 2016
Messages
7
Reaction score
0
Points
0
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 below:

Capture.PNG

[FONT=&quot]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 below: [/FONT]
Capture22.PNG

[FONT=&quot]How can I just only add the total count of categories as a second field to get the view as in the first image?
[/FONT]
[FONT=&quot]Your help is much appreciated! [/FONT]
[FONT=&quot]Thanks[/FONT]
[FONT=&quot]nanka[/FONT]
 
SECOND AND LAST WARNING

Do not crosspost your question on multiple forums without including links here to the other threads on other forums. You were already warned in your first post on this forum !

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
http://www.msofficeforums.com/excel/34803-adding-same-field-column-field-list-pivot.html
 
Last edited:
I am trying to edit the original post and add the links, but the website does not allow saying i need to have 5 posts to insert links. Below is the message preventing me to edit the original post:
To be able to post links or images your post count must be 5 or greater. You currently have 2 posts.

Please remove links from your message, then you will be able to submit your post.
Click to expand...

What should i do here? Website is preventing me to enter links...
 
Nanka,If you have not sorted this puppy send me some fake data to take a quick look at
 
Ed Kelly said:
Nanka,If you have not sorted this puppy send me some fake data to take a quick look at
Click to expand...

Hi Ed,

Thanks so much. I have not found the solution yet.
Here is the file attached called "Sample File". The first view is what i want to achieve with the goal of yellow highlighted column to be part of the pivot table. Now it's not.

The second view is what i am getting when i am trying to do that, basically the counts are shown for each individual category when i only want the total count.

Appreciate your help!

nanka
 

Attachments

  • sample file.xlsx
    90.3 KB · Views: 16
Just caught your post, Not real clear yet on what you exactly want, have a look and let me know where you need it tweaked/overhauled.
 

Attachments

  • sample file.xlsx
    98.5 KB · Views: 12
Ed Kelly said:
Just caught your post, Not real clear yet on what you exactly want, have a look and let me know where you need it tweaked/overhauled.
Click to expand...

What i want here is to have both the PER CENT of each category and the grand total COUNT of all categories in the SAME pivot table. Imagine the pivot-table 4 you created also has the count of all categories IN that same pivot table. In my original tab i am showing in the first view what i want the ultimate pivot table to look like.
Is it possible in Excel or we need VBA for that?

Thanks
nanka
 
Nanka, Please send me last file I sent you with pivot-table 4 and I will adjust, should be a 10 second job. (deleted my original and the one that I sent at 9.36 seems to be confusingly an old file?)
 
Ed Kelly said:
Nanka, Please send me last file I sent you with pivot-table 4 and I will adjust, should be a 10 second job. (deleted my original and the one that I sent at 9.36 seems to be confusingly an old file?)
Click to expand...

Hi Ed,

Here is the file attached that you had sent me. You are not hiding Excel columns right? You are rebuilding the pivot table, right?


Thanks!
nanka
 

Attachments

  • sample file (1).xlsx
    98.5 KB · Views: 7
Nanka,

See attached. To your question, The pivot table is built entirely from your table and the total count is derived from the step just prior to that which is a count of the categories by week (and my understanding is you just want the result you do not want to see the detail supporting that, however the end result is built from the detail). There are times where you might consider hiding certain cols, I do it quite regularly building out some very sophisticated models.

All that said I am still somewhat confused as to what this data is telling you and keep in mind pivot tables are really best used when the data (value field) is numberical.

Enjoy your evening!
 

Attachments

  • sample file (1) (1).xlsx
    100.8 KB · Views: 10
Ed Kelly said:
Nanka,

See attached. To your question, The pivot table is built entirely from your table and the total count is derived from the step just prior to that which is a count of the categories by week (and my understanding is you just want the result you do not want to see the detail supporting that, however the end result is built from the detail). There are times where you might consider hiding certain cols, I do it quite regularly building out some very sophisticated models.

All that said I am still somewhat confused as to what this data is telling you and keep in mind pivot tables are really best used when the data (value field) is numberical.

Enjoy your evening!
Click to expand...

Hi Ed,

Sorry - but I still don't see in your file what i intended to achieve. You hid the columns, which is not my purpose. My final goal is to get a picot chart that will be reading off of the pivot table i am trying to build. I attached the file, where in the tab called "my sheet", if you scroll to the right you will see the chart I am aiming to get. Hiding columns does not help here, because we need the complete data source in the form of pivot table including both the % splits of each category and the sum count of categories per week.

Can you take a look and check if you know a way to build that type of pivot table or not?

Thanks!
nanka
 

Attachments

  • my copy.xlsx
    107.2 KB · Views: 4
Nanka,

Well it has been a while! Clarity and Specificity is the order of the day...your original outcome is not the same as your new outcome so going forward try to let a guy like me know your real end goal not merely a milestone on the way and I will build you that.

See if this puppy works and of course finesse at your leisure.

Knock 'em dead Tiger!
 

Attachments

  • my copy.xlsx
    110.4 KB · Views: 13
You must log in or register to reply here.
Back
Top