• 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.

Pivot table

D

DashboardNovice

Member
I have a field called VALUE which holds the abbreviations of states. In the pivot table window, I have this under the box called Values. I know that Values is typically used for numbers, but when I look at the resulting arrangement of the pivot table, this is exactly what I'm looking for. The only problem is that the pivot table returns a COUNT, instead of the state abbreviation. Is there anyway I can get Excel to make the state abbreviations appear?

The field called VALUE has 75 unique values (some are countries in addition to states) so I can't put that in the column section.

After much trying, I am really close to a solution. I have just this one last hurdle.
 

Attachments

  • Chandoo.org - Pivot Table Fields.png
    Chandoo.org - Pivot Table Fields.png
    4.7 KB · Views: 23
  • Chandoo.org - Pivot Table.png
    Chandoo.org - Pivot Table.png
    7.5 KB · Views: 23
Can you upload a copy of the workbook, or a version you're able to share? Outside of creating a helper column (ex. Value_Text), would moving the state/country abbreviations to Rows fit what you're looking to get from the table?
 
ArmstrongArcade said:
Can you upload a copy of the workbook, or a version you're able to share? Outside of creating a helper column (ex. Value_Text), would moving the state/country abbreviations to Rows fit what you're looking to get from the table?
Click to expand...

Here is a small part of the raw data. I moved the VALUE field to rows, but the problem is that each state abbreviation appears under the column called VALUE, but I want it to appear under the column DELIVER TO STATE 1.
 

Attachments

  • Chandoo.org - Pivot Table.xlsx
    14.7 KB · Views: 6
Hey Dashboard Novice, unfortunately, pivot logic reserves the Values section for numeric data. A power query could handle what you're trying to accomplish. Would combining State and Sales # as a Row suffice? There are definitely bigger brains on this forum who may comment with a better option. Of which, I'd be happy to learn about.
 

Attachments

  • Chandoo.org - Pivot Table.xlsx
    20.4 KB · Views: 3
ArmstrongArcade said:
Hey Dashboard Novice, unfortunately, pivot logic reserves the Values section for numeric data. A power query could handle what you're trying to accomplish. Would combining State and Sales # as a Row suffice? There are definitely bigger brains on this forum who may comment with a better option. Of which, I'd be happy to learn about.
Click to expand...
That's what I figured. Oh well. Thank you for trying.

Combining state with the # (which is supposed to be a %) will not work for what I am doing.
 
You must log in or register to reply here.
Back
Top