Excel 2019 - no row grand total in pivot table since update

%3CLINGO-SUB%20id%3D%22lingo-sub-2294650%22%20slang%3D%22en-US%22%3EExcel%202019%20-%20no%20row%20grand%20total%20in%20pivot%20table%20since%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294650%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20a%20forced%20update%20to%20v.%2016.013901.20366%2064bit%20today.%20Now%20no%20row%20grand%20total%20shows%20in%20a%20pivot%20table%2C%20no%20matter%20what%20I%20do.%20Doesn't%20matter%20whether%20it's%20via%20Power%20Pivot%20%2F%20plain%20old%20Insert%26gt%3BPivotTable%2C%20data%20is%20sheet%20or%20table%2C%20added%20to%20data%20model%20yes%2Fno.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20first%20I%20thought%20I%20had%20some%20kind%20of%20personal%20option%20setting%20overriding%20this%2C%20but%20no.%26nbsp%3B%20This%20could%20be%20a%20consequence%20of%20turning%20off%20row%20totals%20on%20one%20project%2C%20but%20that%20is%20not%20what%20my%20settings%20show%20for%20this%20sheet%20or%20my%20user%20options.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20the%20two%20methods%20for%20turning%20off%20%2F%20on%20row%20and%20column%20totals.%20It%20doesn't%20matter.%20Windows%20has%20been%20rebooted%20day%20today%20due%20to%20forced%20update%2C%20so%20it's%20fresh.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20most%20basic%20form%20of%20this%20attempt.%20I%20know%20I%20can%20add%20a%20calculated%20field%2C%20but%20shouldn't%20have%20to%20every%20time%20I%20need%20a%20simple%20pivot%20table.%20Smells%20like%20a%20bug.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20MS%20would%20stop%20'improving'%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2294650%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2294842%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20-%20no%20row%20grand%20total%20in%20pivot%20table%20since%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037280%22%20target%3D%22_blank%22%3E%40jshirk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20sum%20by%20rows%20you%20need%20to%20have%20sum%20fields%20in%20Columns%20(check%20right%20pane%20for%20PivotTable)%2C%20otherwise%20it's%20nothing%20to%20sum.%3C%2FP%3E%0A%3CP%3EAlternatively%20you%20may%20create%20measure%20or%20calculated%20field%20to%20sum%20all%20other%20fields%20(which%20are%20in%20Rows%20part%20now)%20and%20add%20it%20as%20separate%20column%20to%20PivotTable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2295038%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202019%20-%20no%20row%20grand%20total%20in%20pivot%20table%20since%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2295038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037280%22%20target%3D%22_blank%22%3E%40jshirk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20creates%20a%20grand%20total%20for%20a%20every%20field%20in%20the%20values%20area.%20You%20got%206%20fields%20and%206%20grand%20totals%20(Sum%20of%20...).%20So%20everything%20is%20working%20fine.%3C%2FP%3E%3CP%3ETo%20get%20a%20sum%20of%20the%206%20grand%20totals%20you%20could%20unpivot%20your%20source%20data%20or%20add%20a%20helper%20column%20in%20the%20source%20data%20adding%20the%206%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I had a forced update to v. 16.013901.20366 64bit today. Now no row grand total shows in a pivot table, no matter what I do. Doesn't matter whether it's via Power Pivot / plain old Insert>PivotTable, data is sheet or table, added to data model yes/no.

 

At first I thought I had some kind of personal option setting overriding this, but no.  This could be a consequence of turning off row totals on one project, but that is not what my settings show for this sheet or my user options.

 

I know the two methods for turning off / on row and column totals. It doesn't matter. Windows has been rebooted day today due to forced update, so it's fresh.

 

Attached is the most basic form of this attempt. I know I can add a calculated field, but shouldn't have to every time I need a simple pivot table. Smells like a bug.

 

I wish MS would stop 'improving' Excel.

7 Replies

@jshirk 

To sum by rows you need to have sum fields in Columns (check right pane for PivotTable), otherwise it's nothing to sum.

Alternatively you may create measure or calculated field to sum all other fields (which are in Rows part now) and add it as separate column to PivotTable.

@jshirk 

Excel creates a grand total for a every field in the values area. You got 6 fields and 6 grand totals (Sum of ...). So everything is working fine.

To get a sum of the 6 grand totals you could unpivot your source data or add a helper column in the source data adding the 6 columns.

@Sergei BaklanI'm afraid I don't understand what you're saying. My columns are already sum fields.

@Detlef Lewin this article, and pretty much every article (and video) shows how row totals are added automatic.
https://support.microsoft.com/en-us/office/show-or-hide-subtotals-and-totals-in-a-pivottable-fc4d840...
row grand totals only appear if there are row groupings. In this updated version, I added a row grouping, and still their is neither a row total or row grand total.

I see what the problem is. In this example, columns are generated by discrete data elements (January, February...)
https://support.microsoft.com/en-us/office/overview-of-pivottables-and-pivotcharts-527c8fa3-02c0-445...
If this is the case, then the only way is to add a value field that sums the column sums.

@jshirk 

Yes, you need calculated field or measure which sums other value. When you work with it as with any other field which you sum.