Correct a Power Pivot Average Using the Maximum Weekday Count
Question details
Determine why an attendees-per-day measure returns repeated averages when divided by a 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.
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.
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.
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].
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.
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.
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.

Verify model relationships and revise the measure
Use this approach if the PivotTable row labels do not filter the attendee or weekday-count data as expected.
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.

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.




