Consider the following PivotTable in Excel:
As you can see the Sum of CalcRevenue
per month makes no sense. Excel apparantly FIRST sums the grouped rows and THEN multiplies them.
Any resolution to get the right calculation? I would like to prevent adding an additional column to the source-datatable.
I have tried to add a @ to the formula, but that had no effect.
I have tried to add a @ to the formula, but that had no effect. Neither had formatting the source-table as a DataTable The problem occurs consistently on any multiplication in any pivottable
EDIT Here is a picture and sample of the source-data:
salesmonth salesdate volume price
1 1-1-2025 68 0.35
1 2-1-2025 76 0.72
1 4-1-2025 4 0.78
1 5-1-2025 21 0.97
1 27-1-2025 74 0.01
1 28-1-2025 27 0.63
1 31-1-2025 73 0.19
2 1-2-2025 89 0.16
2 2-2-2025 95 0.53
2 12-2-2025 96 0.16
2 13-2-2025 9 0.08
2 14-2-2025 18 0.47
When you create the pivot table initially, ensure that you check the option to add it to the Data Model:
This will facilitate the creation of a Dax measure (rather than a calculated field):
which measure should be defined as follows:
=SUMX('Range',[volume]*[price])