Pivot Table Issues

Calculate Week-over-Week Bounce Rate Change in an Excel PivotTable

Aamir Naveed AkramAamir Naveed Akram Nov 22, 2026 868 views

Question details

Calculate the week-over-week percentage change in bounce rate from separate bounce and visit measures.

Calculate Week-over-Week Bounce Rate Change in an Excel PivotTable
Product
Microsoft Excel PivotTable
Device & OS
not provided
Scenario
A PivotTable reports bounces and visits by week and marketing channel, and the user needs to compare each week's bounce rate with the previous week's rate.
Observed behavior
A calculated item does not produce the correct week-over-week change when bounces and visits are separate measures.
Before you start

Check that your source data includes dates, marketing channels, bounces, and visits. Define a consistent week start and confirm that previous-week data is available for each comparison.

Solution 1Recommended

Calculate the change with Power Pivot measures

Use measures when your Excel edition supports Power Pivot and you want the calculation to respond to PivotTable filters.

Calculate bounce rate as total bounces divided by total visits in the current filter context. A simple average of row-level rates can be misleading when rows have different visit counts.

The seven-day shift below compares the same days in the preceding week. Use a complete date table and a full-week filter for a full week-over-week comparison.

1
Add the data to the model

Select the source table, choose Insert > PivotTable, and select Add this data to the Data Model. Add a complete date table to the model and create a relationship from its date column to the source date column.

2
Create the bounce rate measure

On the Power Pivot tab, choose Measures > New Measure. Create a measure such as Bounce Rate := DIVIDE(SUM(Data[Bounces]), SUM(Data[Visits])). Replace the table and column names with those in your model.

3
Create the previous-week measure

Choose Power Pivot > Measures > New Measure again and create Previous Week Bounce Rate := CALCULATE([Bounce Rate], DATEADD('Date'[Date], -7, DAY)). Replace 'Date'[Date] with your date table's date column.

4
Calculate and display the change

Create WoW Change := DIVIDE([Bounce Rate] - [Previous Week Bounce Rate], [Previous Week Bounce Rate]). Add the week and marketing channel to the PivotTable rows and add the measures to Values. Format WoW Change as a percentage.

Calculate the change with Power Pivot measures
Percentage change versus percentage points: The formula returns relative change. For example, a rise from 40% to 44% is a 10% increase, or a 4-percentage-point increase. The measure returns blank when the previous rate is zero or unavailable.
Calculate in WPS Spreadsheets

Compare weekly bounce rates in WPS Spreadsheets

Use a weekly summary and worksheet formulas in WPS Spreadsheets to calculate bounce rates and their week-over-week change without a Power Pivot measure.

  1. 1. Open the weekly data: Open your workbook in WPS Spreadsheets. On a worksheet, arrange one summary row per week and channel with week-start dates in column A, channels in B, summed bounces in C, and summed visits in D.
  2. 2. Calculate the current rate: In E2, enter =IFERROR(C2/D2,"") and fill it down. Make sure the week-start dates in column A are consistent and seven days apart.
  3. 3. Calculate the previous rate: In F2, enter =IFERROR(SUMIFS($C:$C,$A:$A,A2-7,$B:$B,B2)/SUMIFS($D:$D,$A:$A,A2-7,$B:$B,B2),"") and fill it down.
  4. 4. Calculate and format the change: In G2, enter =IFERROR((E2-F2)/F2,"") and fill it down. Select columns E through G and choose percentage formatting on the Home tab.
Calculate rates from summed bounces and visits for each week and channel.Use SUMIFS to find the previous week's totals.Open and edit Microsoft Excel XLSX files.Use familiar spreadsheet formulas and formatting tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why is averaging bounce rates sometimes incorrect?

A simple average gives each rate equal weight, even when visit counts differ. For a combined rate, divide the sum of bounces by the sum of visits in the desired week and channel.

What happens if the previous week's bounce rate is zero?

Relative percentage change cannot be calculated with a zero denominator. The DIVIDE and IFERROR formulas shown return a blank rather than a divide-by-zero error.

Can I calculate the change without Power Pivot?

Yes. Summarize bounces and visits by week and channel in a PivotTable, copy the results to a worksheet, and use the rate, SUMIFS, and change formulas in the weekly summary solution.

Why is my previous-week result blank?

Check that a matching channel and the prior week exist, that week-start dates are exactly seven days apart, and that the prior week has visits. For the Power Pivot measure, also check the date table relationship and date filter.