Pivot Table Issues

How to Show YoY Growth Only in PivotTable Year Totals

Maira Mehtab Sep 25, 2026 869 views

Question details

The user needs to configure a PivotTable to display quarterly summaries while restricting the year-over-year (YoY) growth calculation so it only appears at the yearly total level.

How to Show Year-over-Year Growth Only in PivotTable Year Totals

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating a financial or sales report with a combined PivotTable where quarterly totals are required, but YoY comparisons should only apply to annual totals.
Observed behavior: The year-over-year difference incorrectly appears at the quarterly level, while the yearly total row remains blank. The user wants the exact opposite behavior.

Before you start

Ensure your raw data is formatted as a structured table and includes properly formatted year and month columns before loading it into the Data Model.

Solution 1Recommended

Use Power Query and DAX Measure to Filter YoY Growth

Create a DAX measure using the HASONEVALUE function to suppress quarterly YoY calculations and only display the growth at the yearly total level.

To achieve context-specific calculations in a PivotTable, you need to use the Data Model (Power Pivot) rather than standard PivotTable calculated fields. By leveraging DAX (Data Analysis Expressions), you can explicitly detect whether the PivotTable is rendering a quarter or a year row and output the calculation accordingly.

Load Data to the Data Model

Open Power Query to convert your year and month fields into a proper date format. Once formatted, select 'Close & Load To' and check the option to 'Add this data to the Data Model'.

Create the Prior-Year DAX Measure

In the Power Pivot window, create a new measure that calculates the prior-year comparison. You will use PARALLELPERIOD to shift the date context back by one year.

Apply the HASONEVALUE Logic

Use HASONEVALUE on the Year field to detect the current time level. A recommended DAX pattern is: VAR r = DIVIDE(SUM(RawData[Price]) - CALCULATE(SUM(RawData[Price]), PARALLELPERIOD(RawData[Date], -1, YEAR)), CALCULATE(SUM(RawData[Price]), PARALLELPERIOD(RawData[Date], -1, YEAR))) RETURN IF(NOT(ISBLANK(SUM(RawData[Price]))), IF(HASONEVALUE(RawData[Date (Year)]), BLANK(), r - 1), BLANK()).

Add the Measure to your PivotTable

Insert the newly created measure into the Values area of your PivotTable. The YoY calculation will now be suppressed for quarterly rows and only display on the yearly totals.

Understanding HASONEVALUE: The HASONEVALUE function checks if the current filter context for a specific column has exactly one distinct value. It is essential for distinguishing between subtotal rows (which contain multiple quarters) and detail rows.

Free Microsoft Office alternative

Need to manage data effortlessly? Try WPS Office

While complex DAX measures and Power Query are specific to Microsoft Excel, WPS Spreadsheet offers robust standard PivotTables, an extensive formula library, and an easy-to-use interface for your daily reporting needs—completely free.

1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
2. Install the Software: Run the downloaded installer and follow the quick on-screen instructions to set up WPS Office on your device.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files to instantly access standard PivotTables and formatting tools.

Free and lightweight alternative to Microsoft OfficeSeamlessly open, edit, and save Excel (.xlsx) formatsFamiliar user interface requiring zero learning curveBuilt-in standard PivotTables for powerful data summarization

Frequently Asked Questions

Why does my year-over-year calculation show up in quarters but not yearly totals?

By default, standard PivotTable calculations evaluate in the context of the row. If your formula doesn't account for the rollup hierarchy, it may evaluate to a blank at the total level while populating the lower sub-levels.

What does the HASONEVALUE function do in this context?

HASONEVALUE checks if the current filter context for a specific column (like Year or Quarter) has exactly one distinct value. It is commonly used to differentiate between a total row and a detail row in DAX measures, allowing you to selectively hide data.

Can I calculate YoY growth without using the Data Model?

Yes, you can add helper columns directly in your source data using standard formulas like SUMIFS to calculate previous year values. You can then pull these helper columns into a standard PivotTable, though it is less dynamic than a DAX measure.

Excel & Data Problems