Forum Discussion
Calculated Field giving wrong value in Totals in Pivot Table
Calculated field IF(Value>=500,Value,0) giving the same totals as the original value. Please check.
2 Replies
In addition, if to stay on cached PivotTable you may add calculated column to data source instead of adding calculating field to PivotTable.
However, data model PivotTable as Riny_van_Eekelen suggested in general is better.
jindalsteel Unfortunately, that's how regular pivot tables perform. The calculated field is applied to the row level. When in a row with an SNO then it looks if the sum for that SNO is >= 500 and gives the correct result. When in the Total row it look is the Total is >= 500. And it is, so it gives you the Total but not the sum of the SNO's >= 500.
You need to move into Power Pivot and DAX measures to resolve such 'problems'. The attached file contains a basic solution in Power Pivot. If you have never worked with PP or DAX, that's going to be a challenge, as it isn't the most user friendly formula 'language'. But Google is your friend and will lead you to many sources to start learning about it.
