'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Power BI PivotTable Hiding Rows When Grand Total Is Removed
Question details
Specific project rows disappear from an Excel PivotTable connected to a Power BI dataset when the grand total is removed, despite the projects containing data and remaining in the filter list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Modifying a PivotTable layout by disabling the Grand Total for a connected Power BI dataset.
- Observed behavior
- Rows with valid data are hidden from the PivotTable view once the Grand Total is turned off.
Ensure your Power BI dataset is fully refreshed and verify that you have edit access to modify the PivotTable field settings in your workbook.
Enable the 'Show items with no data' Option
Adjusting the PivotTable field settings to force the display of items without data often resolves visibility issues caused by shifting filter contexts.
When connected to an external data model like Power BI, removing the Grand Total can alter the calculation context. This might cause certain rows to evaluate as blank, prompting Excel to hide them by default.
Right-click on any cell within the affected row field of your PivotTable and select 'Field Settings' from the context menu.
In the Field Settings dialog box, navigate to the 'Layout & Print' tab.
Check the box labeled 'Show items with no data' and click 'OK'.
Right-click anywhere on the PivotTable and select 'Refresh' to apply the changes and restore the missing rows.
Verify Power BI DAX Measures and Relationships
Check the underlying Power BI dataset to ensure DAX measures and model relationships are not causing blank evaluations when the grand total context is removed.
Try WPS Office for Powerful Spreadsheet Management
While Power BI live connections are native to the Microsoft ecosystem, WPS Spreadsheet provides robust, built-in PivotTable tools for everyday data analysis. It serves as an excellent, lightweight alternative for managing complex datasets.
Frequently Asked Questions
Why do PivotTable rows disappear when I remove the Grand Total?
Removing the Grand Total changes the filter context of the PivotTable. If the underlying data or measure evaluates to blank under this new context, Excel automatically hides the row to keep the report clean.
How do I force a PivotTable to show empty rows?
You can force empty rows to display by right-clicking the specific row field, selecting 'Field Settings', navigating to the 'Layout & Print' tab, and checking the 'Show items with no data' option.
Can Power BI model relationships affect Excel PivotTable visibility?
Yes. Incorrect data relationships or single-direction cross-filtering in the Power BI model can cause measures to return blank in specific sub-contexts, leading Excel to hide those rows in the PivotTable.
