Lindsay_K
asked on
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Saurabh..
H--Desktop-Persistence-and-Retention--Al
ASKER
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
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
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..
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..
ASKER
Ohhhh...I see. Thank you again Saurabh!!!
However in order to do so i need to look at your data as in how it looks like..
Saurabh...