Calculated field in Pivot table summarizing only by SUM, not by COUNT or AVERAGE

[victorcortes86]

Hi there,

I have been looking everywhere for a solution to my problem to no avail, so I am turning on my bat-signal and hopefully some hero will come to my aid.

I have some daily sales data, with order date and shipment date. I would like to know how many days in average it takes for my company to process the order and ship the product. I created a calculated field that simply substracts one date to the other one, but when I insert a pivot table and try to show the average, Excel is adamant in showing me to total SUM. Whether I choose to show the field as SUM, AVERAGE or COUNT, Excel still shows the SUM, although it changes the header. I have seen this happen with a few other calculated fields and I havent found a way around it. Very frustating!!

I attached this sample. It shows the pivot table with the calculated field, showing the exact same result for SUM, AVERAGE and COUNT

Hope you have some time to help me out.
Thanks!

 

[Chihiro]

PivotTable calculated field always will sum each row. There's no way to get around it using just the standard PivotTable.

You can add helper column to source table. i.e. =[@[Ship Date]]-[@[Order Date]]

See attached.

NOTE: If you have PowerPivot, you could use iterator function to do the same using DAX.

 

[victorcortes86]

Ok, here is another example, equally frustating for me.

Here I have weekly fuel prices of my country. By weekly, I mean that prices change every week. So, the first pivot table shows weekly prices (in córdobas per liter) by date, using monthly and annual fields to group them.

In the second pivot table, I have a calculated field: dollars per gallon. Here I multiply the C$ per liter * 3.785 * ExchangeRate. Average per month and year are calculated correctly.

In the third pivot table, I have another calculated field: cordobas per gallon. Here I just multiply the C$ per liter * 3.785, which is even a simpler calculation than the second one. However, you can see that monthly and annual totals are summed, not averaged.

Both have calculated fields, and basically the same calculation is performed, but the third pivot table adamantly shows SUM, instead of AVERAGE, as the second one does.

Do you have any idea why?

 

[Chihiro]

Have a read of post #2 in link.
https://chandoo.org/forum/threads/pivot-table-calculation-field-results.32852/
Specifically...

However, PivotTable calculated field works by doing SUM of each column (based on row label grouping) before any other calculations are performed.