Choose the population behind a grouped report

Choose records before creating groups

An order row contains a region, channel, signed whole-unit quantity and status. The course selects Posted rows. Draft and Void rows contribute neither amounts nor axis labels. A region with only draft rows stays out of the report.

A posted return has negative units. A posted zero still counts as an observation. GROUPBY with SUM combines every selected record for each region, including returns and groups whose signed total is zero.

Choose the relationship between grouping fields

When GROUPBY receives Region and Team as two grouping columns, field_relationship=0 keeps a hierarchy. Team sorting remains within each Region. With field_relationship=1, the complete Region/Team combinations form a table: a sort vector such as {2,1} can put Team first across all regions.

A hierarchy has parent groups whose subtotals can be recomputed from their source records. Omitting total_depth includes the available hierarchy subtotals. A table has no parent groups and supports only a grand total; choose depth 0, 1 or -1. The editable GROUPBY relationship examples use the same source to make these differences visible.

Keep the record count behind a mean

EAST/WEB contains 12, -2 and 6 units: sum 16 across three observations. EAST/STORE contains one observation of 5. The EAST mean is (16+5)/(3+1)=5.25. Averaging the two displayed cell means gives the two channels equal weight and changes the question.

With AVERAGE as the PIVOTBY reducer, the Total row and column show means of their original observation subsets. In this example, all six posted observations sum to 29, so the grand mean is 29/6. Read each total using its reducer.

Distinguish a missing combination from a measured zero

An intersection with no selected order displays empty text. An intersection containing a posted zero displays numeric zero. Zero participates in COUNT and AVERAGE; an absent observation has no record to count.

Filter using the selected record count when deciding whether there is a report. A source with posted units 10 and -10 is active even though its signed sum is zero.

Name the whole used for a share

PERCENTOF divides the sum of an intersection by the sum of a second population. PIVOTBY relative_to=2 supplies all selected records as that second population. Mode 0 uses the column population and mode 1 uses the row population.

For the same six posted observations, EAST/WEB contributes 16/29 of grand net units. Signed returns may produce a negative share or a share above one. The result is a decimal proportion; percent formatting changes its display.

A zero grand net makes these shares undefined, including cases with many posted records. The course returns No net units before division. This state describes the denominator and does not claim there was no activity.

Keep the output area clear as axes change

Each task uses one formula anchor. Changing the selected source population can grow or shrink both axes, so reserve the complete graded area. A missing cell inside an expected spill and an extra result outside its actual extent are checked separately.

These functions produce formula spills. The reporting workshop also teaches native PivotTable objects; the two workflows have different controls and download structures. Browser calculations and native Excel recalculation remain separate compatibility checks.

Try the idea

• Group posted units →
• Cross-tabulate order means →
• Report grand net shares →
• Complete the grouped reports course →
• Explore PivotTable objects →

References

• Microsoft: GROUPBY ↗
• Microsoft: PIVOTBY ↗
• Microsoft: PERCENTOF ↗

Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.