Pivot Table - Calc Field

porkchops2

New Member
Joined
Nov 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am struggling to figure out how to generate a calculated field within my pivot table. Below is an example of my pivot table and the Discrepancy column is what I need to generate through a formula. I've also included a copy of my source data. I need to know when the total of the line discounts for an invoice do not match with the discount amount for an invoice.

1604605554882.png


1604605764751.png
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Select 'Fields Items & Sets' > 'Calculated Field...' from the pivot table menu, then enter the formula as 'Line Discount' - Discount.
 

porkchops2

New Member
Joined
Nov 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Unfortunately, that formula appears to be summing the Discount column and gives an invalid sum total if the invoice has more than one line.

1606164903887.png
 

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Unfortunately, that formula appears to be summing the Discount column and gives an invalid sum total if the invoice has more than one line.

View attachment 26599
Will the 'Discount' be the same for each row? If so, you could add a helper column containing the Discount/The number of rows for that invoice and use that in your calculated column.

=Discount/COUNTIF(Invoices,Invoice)

Therefore the sum of all of these should add up to the original figure.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top