Excel Pivot Table Column

Occasional Contributor

Hello all,

 

Is there a way to make my own column in a pivot table? I'll provide the existing table for reference: 

jwenzCHUBB_0-1656363550906.png

I would like to make a column that simply outputs the difference between "Declined" and "Total" 

 

I know I can make a column next to it that easily does this but if the column can somehow exist within the existing table, that will be of great help.

 

Thank you!

3 Replies

@jwenzCHUBB 

You need to add aggregation within PivotTable, external column doesn't work. In general it depends on how do you calculate Total. As variant creating PivotTable you may add data to data model and create DAX measures for any desired aggregations.

Thanks, I'm an amateur with this. Can you help more specifically?

The total is calculated by the sum of rows existing under the same received date,

Using the example above: On May 16th, there were 11 total items created and 4 of them were declined. Thus 7 is the number I would like to have in another column.

The reason being is I plan to automate an email that sends out the pivot table and although it is quite easy to do the math, my boss is asking otherwise.

I can add a column to the table the pivot table is pulling from if this might help? Thanks for the help and suggestions as always!

@jwenzCHUBB 

I assume you are on Excel Desktop on Windows.

Creating PivotTable add data to data model

image.png

Add date to rows in PivotTable Fields pane.

Right click on table name and Add measure

image.png

Add 3 measures one by one, it looks like

image.png

Measures are

Total:=COUNTROWS( Table1 )
Declined:=CALCULATE( [Total], Table1[IsDeclined] = "Yes" )
Not Declined:=[Total] - [Declined]

Add measures to values

Finally

image.png