PowerPivot - How to separate nesting row labels?

I have created a PowerPivot 2013 PivotTable in Excel due to the need to calculate distinct values for numerous calculations that cannot be efficiently calculated using array formulas.  So I will not be able to use the normal Excel Pivot Table features.

I am using:
3 columns in a table for the Row Labels (Cohort Year, Gender, Ethnicity)
1 column in the table for the Column Labels (Major_1)

This seems like it should be simple, but I can't find out how anywhere?
I would like to see the rows group together and nest both gender and ethnicity into the year, but not into each other...as follows:

2005
  Male
  Female
  Hispanic/Latino
  Not Hispanic/Latino
  Unknown
2006
  Male
  Female
  Hispanic/Latino
  Not Hispanic/Latino
  Unknown
2007...

However, the Pivot Table insists on nesting the Ethnicity inside of the Gender or vice versa.  It simply will not separate them out into separate categories.  This is what it look like now:

2005
  Female
    Hispanic/Latino (Females)
    Not Hispanic/Latino (Females)
    Unknown (Females)
  Male
    Hispanic/Latino (Males)
    Not Hispanic/Latino (Males)
    Unknown (Males)
2006...

Many thanks in advance for your help.

-Lindsay
Lindsay_KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you update your sample data in context.. I can build my powerpivot on top of it myself to give you solution of what you are looking for..

However in order to do so i need to look at your data as in how it looks like..

Saurabh...
0
Lindsay_KAuthor Commented:
Thank you for your response Saurabh,
I have attached the workbook.  The ID numbers are not real since the data could be viewed as sensitive, but I duplicated a few of them to demonstrate that the field does not contain unique values in case it matters.
H--Desktop-Persistence-and-Retention--Al
0
Saurabh Singh TeotiaCommented:
Lindsay,

I had a look over your file and parameters you added were correct,However the view that you are looking for won't be possible and the reason for the same is that in pivot tables in row fields it groups the headers to show the data, Now your gender and language are two separate headers so its grouping them accordingly when you drag them..so when you drag them in the row field..the first it shows the first item and then the next item and then next and so on..

The way it works is 1-->2-->3 ..However what i understand you want to do..

1-->2 & 1-->3

You can't do that..if you drag and drop them in row field ...

Alternatively you can make 2 pivots one show the count of gender and other shows on language ..or you can drop both of them on the report filter and play arround with it to see the necessary view which you are looking for...

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Saurabh Singh TeotiaCommented:
Enclosed is your workbook where i made two pivot tables for your references.. which shows the view what you are looking for...

Saurabh..
H--Desktop-Persistence-and-Retention--Al
0
Lindsay_KAuthor Commented:
Thank you again Saurabh,
I wasn't able to open your attachment for some reason.  I am at work, so it is quite possibly a restriction on my end.  I appreciate your answer.  The only thing I do not understand in your response is what the "report filter" is?

Thank you so much,

Lindsay
0
Saurabh Singh TeotiaCommented:
Once you download the file.. add the .xlsx extension name at the end and you will be able to open it like a normal excel file posting adding that...

by report filter i mean showing them in filter area of the pivot table that way you can select the option of whatever data you want to see by applying that filter.. on that particular criteria..
0
Lindsay_KAuthor Commented:
Ohhhh...I see.  Thank you again Saurabh!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.