# grand total of a sub-column5

#### alex1alex

##### Board Regular
Hello,

This might be a straight pivot table question but posting here as my data is in Power Pivot.

I need to find the grand-total of a sub-column.
So, in the example XLS, (I just went and grabbed some census data) I would like to have the grand total of whites and blacks as the last right 2 columns of the pivot table.

Is this possible?

Thanks,
Alex

I'd like the pivot table to look like:

 Sum of value enrollment whiteORblack Enrolled in grade 1 Enrolled in grade 2 Enrolled in grade 3 black white black white black white total black total white Total 17820 39923 18670 39107 18755 41546 55245 120576

<tbody>
</tbody>

<tbody>
</tbody>

currently the pivot table fields look like:
 COLUMNS VALUES ENROLLMENT Sum of POPULATION whiteORblack

<tbody>
</tbody>

 ENROLLMENT POPULATION whiteORblack Enrolled in grade 1 39,923 white Enrolled in grade 2 39,107 white Enrolled in grade 3 41,546 white Enrolled in grade 1 17,820 black Enrolled in grade 2 18,670 black Enrolled in grade 3 18,755 black

<tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Tianbas

##### Board Regular
Why you want to have this strange format ? If you put whiteorblack in rows your table is much easier to read and you automatically get your subtotals.

If it has to be that kind of format for whatever reason you could create a measure for white and a measure for black that you can use instead of sum of population.
=CALCULATE(sum(Table1[POPULATION]),Table1[whiteORblack]="black")

Another solution could be create a set under options->field items and sets->create set based on column options.

#### alex1alex

##### Board Regular
Why the strange format? Because this is just an example file In my work file, the rows are populated with 2/3 other fields. I can't really move/delete them. Yeah?
Also, the main point of the file is to compare white vs black. (both by grade and total).

Where is this located? -> Another solution could be create a set under options->field items and sets->create set based on column options.

1,089,394
Messages
5,407,994
Members
403,176
Latest member
mehtavish1