Understand how percentages are calculated in Tables

What you'll learn

By the end of this article, you'll be able to:

• Distinguish between how percentages are calculated for Totals versus Subtotals
• Identify chart and table configurations where percentage values may be logically incorrect

Before you begin: This article assumes you can already create a table and have configured at least one Table Total or Subtotal with the Aggregate Function Sum. If you haven't, review the Table Totals Guide first.

Introduction

Percentage formatting for Table Totals and Subtotals is now available in beta. Tables in One Model support percentage formatting on Totals and Subtotals, letting you display each cell's share of the overall total alongside — or instead of — raw values. This article explains how the calculation works across different table configurations, and where to exercise caution.
Use the Feedback form if you would like to provide Feedback.

Percentages in tables are always calculated per column — each column sums to 100%. Dimensions appear as rows on the left-hand side of the table; Pivoted Dimensions appear as columns.

Tables with Dimensions and Pivoted Dimensions

Each Pivoted Dimension creates a separate column. Percentages are calculated independently per column, so each column sums to 100%.

Tables with multiple metrics

Without a Pivoted Dimension: Each metric has its own column. Percentages and sums are calculated independently per metric column.

With a Pivoted Dimension: Metrics are listed below one another in the same column. In this layout:

• A Subtotal sums each metric independently (e.g. EOP Headcount – Female and EOP Headcount – Male each have their own Subtotal).
• A Table Total adds values across all metrics in the column.

Note: Not all multi-metric totals are meaningful. For example, summing Headcount and Headcount – Managers double-counts managers. Enable Table Totals only when the aggregate makes logical sense for your data.

Quick reference — percentage calculation in tables

Considerations for accurate percentages

When Table Totals are blank

Metrics can be formatted as % or as absolute values. Table Totals cannot sum metrics with different format. When the Table Total is missing, indicated by /, the percentages will also be missing, indicated by (-).

Data across multiple levels

When a table includes more than one level of a hierarchy, the Table Total sums all levels — counting parent nodes and their child nodes separately. This causes parent-level rows to appear overstated.

For example, if a Director node is the parent of all Managers below it, the Total will count the Director's headcount twice. To get accurate percentages, filter the table to include only the level you need.

This can be corrected by setting the Total Logic to  top level or bottom level values which corrects the Total display — but percentages still calculate across all hierarchy levels in the data set and may remain incorrect.

Metrics formatted as percentages

Some metrics are defined as percentage values rather than absolute counts (for example, Managers as %). Summing these across rows or enabling a Table Total does not produce a meaningful result, and the resulting percentage of Total will also be incorrect. Avoid enabling Table Totals on metrics of this type.

Pivoted Columns

Pivoted Columns are not currently supported by Table Totals.

Filters and Role Based Security

Percentages recalculate based on the data shown in the table. When a filter is applied or a user has Role Based Security restrictions, percentages and Table Totals reflect only the accessible data slice.

Exports

Percentages are not included in CSV exports.

Decimal formatting

Percentages in tables default to one decimal place and are rounded accordingly.

Feedback Form

We invite our beta-testers to complete this FEEDBACK FORM to share what they liked about this feature, any bugs they encountered, and suggestions for future development. 

Next steps

Now that you can configure percentage formatting in Tables, you might want to:

• Display percentages in Basic Charts — enable percentage Data Labels on column charts
• Configure Table Totals and Subtotals — set up and manage Totals with different Aggregate Functions