grand total of a sub-column5

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
51
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 valueenrollmentwhiteORblack
Enrolled in grade 1Enrolled in grade 2Enrolled in grade 3
blackwhiteblackwhiteblackwhitetotal blacktotal white
Total17820399231867039107187554154655245120576

<tbody>
</tbody>

<tbody>
</tbody>



currently the pivot table fields look like:
COLUMNSVALUES
ENROLLMENTSum of POPULATION
whiteORblack

<tbody>
</tbody>



ENROLLMENTPOPULATIONwhiteORblack
Enrolled in grade 139,923white
Enrolled in grade 239,107white
Enrolled in grade 341,546white
Enrolled in grade 117,820black
Enrolled in grade 218,670black
Enrolled in grade 318,755black

<tbody>
</tbody>

<tbody>
</tbody>
 

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
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
Joined
Sep 12, 2014
Messages
51
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.
 

Forum statistics

Threads
1,078,515
Messages
5,340,863
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top