Pivot Table Issues

How to Show PivotTable Values as Counts and Percentages

Tauseeq Magsi Sep 28, 2026 869 views

Question details

The user needs to display a single PivotTable field as both a count and a percentage of the Grand Total, specifically while filtering for a certain value (like 'Y').

How to Show PivotTable Values as Counts and Percentages

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a specialized PivotTable layout that aggregates a single field in two different ways (count and percentage) based on a specific filter criterion.
Observed behavior: Standard PivotTables cannot create this specialized layout without duplicating other fields, requiring the use of the Excel Data Model and Power Pivot measures.

Before you start

Ensure that the Power Pivot add-in is enabled in your Excel application, as you will need to add your data to the Data Model to create custom DAX measures.

Solution 1Recommended

Use the Data Model and Power Pivot Measures

This is the recommended approach to display both a count and a percentage for a filtered value without disrupting the PivotTable layout.

Because a standard PivotTable does not support this specialized filtered layout inherently, you must add your source data to the Excel Data Model. This allows you to write custom DAX measures for both the count and the percentage of the Grand Total.

Add Data to the Data Model

Select your source data range, navigate to the 'Insert' tab, and click 'PivotTable'. In the creation dialog, check the box for 'Add this data to the Data Model' and click 'OK'.

Create a Count Measure

In the PivotTable Fields pane, right-click your table name and select 'Add Measure'. Write a DAX formula for the count (e.g., using the COUNTA function) and name the measure.

Create a Percentage Measure

Right-click the table name again to add a second measure. Write a DAX formula that divides your count measure by the overall total, and set its formatting to Percentage.

Configure the PivotTable Layout

Drag both of your newly created measures into the 'Values' area of the PivotTable, then apply a filter to the relevant field to only show values marked as 'Y'.

Use the Data Model and Power Pivot Measures

Custom DAX Formulas: The exact DAX syntax will depend on your column headers. You may need to use the CALCULATE and ALL functions to accurately determine the Grand Total denominator.

Solution 2

Duplicate Fields in a Standard PivotTable

If you do not have access to Power Pivot, you can use a standard PivotTable by dragging the field into the Values area twice.

Free Microsoft Office alternative

Try WPS Office for Powerful Data Analysis

While advanced Data Model DAX measures are specific to Microsoft Excel, WPS Office provides a robust, free, and lightweight alternative for data analysis. With WPS Spreadsheet, you can easily create advanced PivotTables, duplicate fields to show counts and percentages, and maintain full compatibility with your data.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbook directly without formatting loss.
3. Insert Your PivotTable: Go to the Insert tab, create a PivotTable, and drag fields into the Values area to calculate counts and percentages.

Fully compatible with Microsoft Excel (.xlsx) formatsEasily create standard PivotTables with multiple value field settingsFree, lightweight, and features a highly familiar user interfaceSeamless file migration across Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Can I show counts and percentages in a PivotTable without using Power Pivot?

Yes. In a standard PivotTable, you can drag the same field into the Values area twice. Set the first field to summarize by 'Count' and configure the second field's settings to 'Show Values As' > '% of Grand Total'. However, complex filtered layouts may still require Power Pivot.

Why is my PivotTable % of Grand Total showing an error?

Errors usually occur if the base field contains blank cells or incorrect data types, or if the field is mistakenly summarizing as a 'Sum' instead of a 'Count' before calculating the percentage.

What is the Excel Data Model?

The Data Model is a powerful engine built into Excel that allows you to integrate data from multiple tables and write advanced DAX (Data Analysis Expressions) measures for complex PivotTable calculations.

Excel & Data Problems