Pivot Table Issues

Correct a Power Pivot Average Using the Maximum Weekday Count

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

Question details

Determine why an attendees-per-day measure returns repeated averages when divided by a maximum weekday count.

Correct a Power Pivot Average Using the Maximum Weekday Count
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A PivotTable uses a Power Pivot measure that divides the sum of attendees by the maximum number of weekdays.
Observed behavior
The average and the maximum weekday count appear to repeat across PivotTable rows.
Before you start

Identify the source columns for attendees and weekday counts, and decide whether the expected average is per month or per individual weekday. Note the expected result for at least one PivotTable row so you can compare it with the measure.

Solution 1Recommended

Check the numerator and weekday maximum in the PivotTable

Use this approach to identify whether the repeated average comes from the attendee total, the denominator, or the row filter context.

1
Inspect the existing measure

In Excel, open Power Pivot > Manage and locate the average measure in the calculation area. Check whether it divides [Sum of Attendees] by [Max of No Weekdays].

2
Show both components

In the PivotTable Fields pane, add [Sum of Attendees] and [Max of No Weekdays] to Values alongside the average. Compare all three values across the rows where the average repeats.

3
Clarify the maximum

In Power Pivot > Manage, inspect the source column used by [Max of No Weekdays]. Determine whether its values represent weekdays in a month, occurrences of a particular weekday, or another count.

4
Compare with the expected result

Select a PivotTable row and calculate its expected attendee total divided by the appropriate count from the source data. If the denominator is a monthly count, it may not be appropriate for an individual weekday row.

Check the numerator and weekday maximum in the PivotTable
A repeated maximum is not always an error: If each row is evaluated against the same monthly weekday count, the maximum can legitimately repeat. The average still needs to be checked against the intended calculation.
Free Microsoft Office alternative

Explore spreadsheet data with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface and support for Microsoft Excel formats, making it easy to move everyday spreadsheet work. It does not fix Excel Power Pivot model or DAX measure issues.

Review and organize spreadsheet data in a familiar interfaceWork with Microsoft Excel XLS and XLSX formatsUse a free, lightweight suite for everyday office tasksMove common spreadsheet workflows from Microsoft Office with ease
QA img-9

Frequently Asked Questions

Why does the maximum weekday count repeat on every row?

The measure may be evaluated at the month level, or the row field may not filter the table containing the count. Add the maximum measure to PivotTable Values and check the relationships in Power Pivot > Manage > Diagram View.

Does a monthly weekday count work for an individual weekday average?

Not necessarily. A monthly count covers all weekdays, while an average for a particular weekday may need the number of occurrences of that weekday. Check the source data and the intended result before changing the denominator.

How can I tell which part of the average is causing the repeated value?

Add the attendee sum and maximum weekday count separately to the PivotTable Values area. Compare each component across the affected rows and against a manual calculation.

Can WPS Office repair my Power Pivot DAX measure?

No. Use Excel Power Pivot to inspect and correct the model, relationships, and DAX measure. WPS Office is an alternative for everyday spreadsheet work with Excel-compatible files.