grand total of a sub-column5

[alex1alex]

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>

 

[Tianbas]

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]

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

I actually already had that measure....where do i put it?

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