Excel pivot table calculated field not behaving as expected

Question

Here is an example of the sort of data I'm working with:

I would like to create a pivot table where the rows are the dates, the columns are the regions, and the values are the percent of total daily transactions that came from that region. So my desired output looks like:

I created a Calculated Field called "PercentOfDailyTransactions" with the formula COUNT(Amount)/'Total Daily Transactions'. Then I created a pivot table with value equal to Sum of PercentOfDailyTransactions.

However, this appears to be calculating 1/(total daily transactions x number of transactions in that region on that day), rather than (number of transactions in that region on that day)/(total daily transactions). For instance, 1/2/2021 in West region should be 3/6=0.5 but is instead 0.0555=1/(3*6). How is Excel getting this value, and how do I create a calculated field that calculates my desired output?

Note that if I add a column to my source table with the formula COUNT([@Amount])/[@[Total Daily Transactions]], then create a pivot table whose values are the sum of that field, that gives me the correct result. However, I would like to be able to accomplish this without adding such a column.

question from:https://stackoverflow.com/questions/65892603/excel-pivot-table-calculated-field-not-behaving-as-expected

Answer 1

Optional step: I would format your base data set as a table. It might help you later if you're creating more Pivot charts, reporting connections, adding slicers, etc.

Here's what I got

Steps:

1. Select your data and create a defualt Pivot table
2. In the PivotTable Fields, drag region into columns, date into rows, and total daily transactions into values. The default for total daily transactions in the Values field is set to Sum, so left click that -> Value field settings -> Count
3. Right click on any data point in the Pivot table -> Show value as -> % of row total
4. Done

You can repeat the same steps for Amount, excpet that in the Value field settigns, you keep that as Sum.