Percent of Total That Doesn't Add Up
A sales-ops manager builds a text table: Region on Rows, QUARTER(Order Date) on Columns (2024 Q1–Q4), SUM(Sales) on Text, then applies Quick Table Calculation → Percent of Total to the Sales pill. Each region's four cells sum to 100% - but she wants each column to be that region's share of the quarter total, so each column should sum to 100%. The grand total row at the bottom shows a percentage that is nowhere near 100. She then filters to Category = 'Furniture' and every percentage on the sheet shifts, including quarters and regions she expected to be unaffected. Finance will screenshot this view, so whatever you build has to survive a filter change and a total row.
1. Why does each row sum to 100%, and exactly what do you change?
Read the worked answer
A quick Percent of Total is a table calculation, and its default Compute Using is Table (across) in this layout. That splits the view into partitions - one per Region, since Region is the "other" dimension - and computes each quarter's share of the row total. Rows summing to 100% is the calculation doing precisely what it was told.
Right-click the pill → Edit Table Calculation → Compute Using → Table (down) (or Specific Dimensions with Quarter ticked as addressing and Region unticked). Now Region partitions the denominator and Quarter addresses within it, so each quarter column sums to 100%.
State the rule you are applying: partitioning decides the group the denominator covers, addressing decides which marks share a computation. Put both dimensions in addressing and you get the diagonal mess where exactly one cell per row and column reads 100%.
Check it numerically: the four region cells in the Q3 column should add to 100.0, and no single cell should equal 100.
2. Why is the grand total row wrong, and what are the options for fixing it?
Read the worked answer
Grand totals are produced by a separate query against the data source, and table calculations are not evaluated on that query. The total row therefore displays the underlying SUM(Sales) formatted with your percentage format - mathematically the true total, but as a percentage it reads like a four-digit number. It is a different code path, not a rounding bug.
Options, best last:
- Hide it - Analysis → Totals → turn off, and build your own total column. Fast, but you now own the arithmetic.
- Keep the table calc and accept that the total is raw - only viable if nobody reads the bottom row, which is not the case here.
- Replace the table calc with a query-level ratio:
SUM([Sales]) / SUM({ FIXED DATEPART('quarter',[Order Date]) : SUM([Sales]) }). Because the denominator is an LOD computed in the query, it appears correctly in totals and survives sorting and re-layout.
For a view that gets screenshotted by finance, option 3. Reconcile it to option 1 on one quarter to be sure they agree.
3. The Category filter moved every percentage. Explain why, and build a version whose denominators ignore that filter.
Read the worked answer
Order of operations. Dimension filters are applied before table calculations, so filtering to Furniture removes rows, the row totals shrink, and every percentage is recomputed against the smaller denominator. That is correct arithmetic, not a quirk - and usually it is what you want.
If you want denominators frozen at the all-category figure while the numerator responds to the filter, the calculation has to be evaluated earlier than the filter - which is what a FIXED LOD does:
SUM([Sales]) / SUM({ FIXED [Region], DATEPART('quarter',[Order Date]) : SUM([Sales]) })
FIXED is computed at the data-source level, before dimension filters, so the denominator stays at the full-category number while the numerator filters down. The column will intentionally sum to less than 100% once a category is excluded - that is the feature, so label the axis or tooltip accordingly.
The trap to call out: a filter promoted to Context is applied before FIXED, so putting Category on Context silently restores the old behaviour. Test by selecting one category and confirming the column total drops below 100%; if it still reads 100%, the filter is in the wrong place.