Pivot Table Issues

Calculate Average Daily Sales by Year and Month in Power Pivot

Algirdas Jasaitis Oct 14, 2026 869 views

Question details

The user needs to create a Power Pivot DAX measure that accurately calculates average daily sales, dynamically adjusting its divisor (working days) depending on whether the PivotTable displays a collapsed year or expanded months.

How to Calculate Average Daily Sales by Year and Month in Power Pivot

Product: Spreadsheet software with Power Pivot
Device & OS: not provided
Scenario: Building a PivotTable report where expanding or collapsing a date hierarchy needs to automatically adjust the average daily sales calculation based on working days.
Observed behavior: The current calculation incorrectly sums working days directly from the fact table, yielding inflated divisors (e.g., 622 days instead of 308) and inaccurate averages.

Before you start

Ensure your Data Model includes a separate Working Days or Date lookup table properly related to your main sales fact table to prevent duplicated day counts.

Solution 1Recommended

Create a Context-Aware DAX Measure Using DIVIDE

Use the DAX DIVIDE function to dynamically divide total sales by working days referenced from a dedicated dimension table.

When calculating daily averages in a PivotTable, summing working days directly from the sales fact table inflates numbers due to repeated rows per date. Instead, the working days must be aggregated from a related lookup table containing unique monthly values.

By setting up the correct relationship, a single measure will adapt to the PivotTable's filter context, providing accurate annual averages when collapsed and monthly averages when expanded.

Relate tables in the Data Model

Open the Power Pivot window. Ensure both your Sales table and your Working Days table are present. Switch to Diagram View and drag the Date or Month column to create a relationship between the two tables.

Create the new DAX measure

Select the calculation area in Power Pivot beneath your Sales table. Enter the formula: =DIVIDE(SUM('SalesTable'[TotalSales]), SUM('WorkingDaysTable'[WorkingDays]), 0)

Configure PivotTable rows

Insert a PivotTable. Drag the Date hierarchy (Year and Month) from the Working Days table into the Rows area.

Apply the measure to values

Place your newly created DAX measure into the Values area. Expanding and collapsing the year hierarchy will now dynamically divide sales by the correct working days.

Create a Context-Aware DAX Measure Using DIVIDE

Dynamic Calculation Context: Because of filter context, this single measure will automatically divide by the yearly total (e.g., 308 days) when collapsed, and the monthly working-day total when expanded.

Free Microsoft Office alternative

Perform Advanced Data Analysis with WPS Office

While Power Pivot and DAX modeling are specific to Microsoft Excel, WPS Office provides excellent, built-in spreadsheet capabilities and advanced PivotTables. If you need a free, lightweight alternative to manage large datasets without complex data modeling extensions, WPS Office is the ideal solution.

1. Download and Install: Get WPS Office for free from the official website and follow the straightforward installation guide.
2. Open Your Excel Files: Launch WPS Spreadsheet and open your existing .xlsx files. Your standard PivotTables will load immediately.
3. Explore Calculated Fields: Use WPS Spreadsheet's built-in PivotTable analyzer tools to add custom calculated fields for streamlined daily reporting.

Highly compatible with Microsoft Excel (.xlsx) formats and standard PivotTables.Familiar, intuitive user interface for seamless migration and zero learning curve.Free and lightweight Office suite that runs smoothly on most operating systems.Powerful built-in data summarization and calculation fields.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my daily sales average calculation showing an incorrect, inflated number?

This commonly happens if you sum working days directly from the sales fact table. Because a fact table contains many transaction rows per day, the working days get improperly multiplied. Always sum working days from a separate Date or Monthly lookup table.

Can a single Power Pivot measure calculate both yearly and monthly averages accurately?

Yes. Because DAX measures respect the PivotTable's filter context, a properly structured DIVIDE measure evaluates its environment dynamically. It divides by the year's total working days when the row is collapsed, or by the specific month's working days when expanded.

What is the benefit of using the DAX DIVIDE function over a standard slash (/) operator?

The DAX DIVIDE function is designed to safely perform divisions by handling 'divide by zero' errors natively. It includes a built-in parameter for an alternate result (such as 0 or a blank), preventing error messages when a specific period lacks working days or sales data.

Excel & Data Problems