excelexcel-formulapivot-table

Grouped items in Excel PivotTable do not provide the right outcome when using a calculated field


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.

pivotTable

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:

Pivottable settings

TablePic

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

Solution

  • When you create the pivot table initially, ensure that you check the option to add it to the Data Model: Screenshot illustrating option to add to Data Model

    This will facilitate the creation of a Dax measure (rather than a calculated field): Screenshot illustrating impact of measure on pivot table

    which measure should be defined as follows: Screenshot illustrating definition of measure

    =SUMX('Range',[volume]*[price])