Why Your CPM, CTR, and CPC Totals Look Wrong in Power BI, Data Studio, or Google Sheets (and How to Fix Them)

If the totals row in your report shows a CPM, CTR, or CPC that does not match your ad platform, your data is fine: the totals row is doing the math the wrong way. This guide explains why it happens and shows you how to get totals that match the platform, usually in a few minutes.

Why This Happens

CPM, CTR, and CPC are ratio metrics, which means every row is already a division. For a single row, CTR is that row's clicks divided by that row's impressions, CPC is that row's cost divided by its clicks, and CPM is that row's cost divided by its impressions, times 1,000.

When your reporting tool builds a totals row, it often sums or averages the per-row ratio values. That produces the wrong number, because a small row (a few impressions) is counted equally with a large row (thousands of impressions). The result drifts away from reality.

The fix is the same everywhere: make the total redo the division from the base numbers (cost, impressions, and clicks), which are already in your PMA data.

A real example. One advertiser's report showed a total CPM of €3.16 when the correct figure was €2.50, and a CTR of 5.30% when the correct figure was 3.93%. The underlying data was identical; only the totals-row math was wrong. Recomputed from the totals (spend €4,445.92, impressions 1,777,432, link clicks 69,916), CPM is €2.50 and CTR is 3.93%.

The Rule Behind the Fix

Totals for ratio metrics must be calculated from summed base metrics, not from the ratio column:

• CTR = total clicks ÷ total impressions
• CPC = total cost ÷ total clicks
• CPM = (total cost ÷ total impressions) × 1,000

Your PMA data already includes the base columns (cost or amount spent, impressions, and clicks), so no change is needed on the PMA side. Everything below happens in your reporting tool.

Before you start, confirm your export includes the base columns (cost or amount spent, impressions, and clicks). If any are missing, add them to the field selection for that data source in the hub and they will flow through on the next sync.

Fix It in Power BI

In Power BI, create a measure for each ratio metric instead of aggregating the imported ratio column. A measure recalculates the ratio from the summed base columns, so both the rows and the totals row come out correct.

1. Select your table in the Data or Model view.
2. For each metric, choose New measure and enter a formula:

CTR = DIVIDE(SUM('Table'[Clicks]), SUM('Table'[Impressions]))
CPC = DIVIDE(SUM('Table'[Cost]), SUM('Table'[Clicks]))
CPM = DIVIDE(SUM('Table'[Cost]), SUM('Table'[Impressions])) * 1000

3. In your table or chart, replace the imported ratio columns with these new measures.

Swap 'Table' and the column names for the ones in your data. (For a Meta Ads export, for example, the columns are typically named Amount Spent, Impressions, and Link Clicks.) DIVIDE also protects you from divide-by-zero errors when a row has no impressions or clicks.

Fix It in Data Studio

In Data Studio, create a calculated field for each ratio metric and use it in place of the per-row column.

1. In your report or data source, add a New field (calculated field).
2. Enter a formula for each metric:

CTR = SUM(Clicks) / SUM(Impressions)
CPC = SUM(Cost) / SUM(Clicks)
CPM = SUM(Cost) / SUM(Impressions) * 1000

3. Use these calculated fields in your charts and tables instead of the imported ratio fields.

Replace Clicks, Impressions, and Cost with your actual field names. Because the calculation sums the base fields first, the chart total and any row-level values are both correct.

Fix It in Google Sheets

In Google Sheets, the imported values in each row are correct; only the totals row needs different formulas. Instead of summing or averaging the ratio columns, divide the column totals.

1. In your totals row, do not use SUM or AVERAGE on the CPM, CTR, or CPC columns.
2. Enter formulas that divide the summed base columns instead, for example:

CTR total:  =SUM(Clicks range) / SUM(Impressions range)
CPC total:  =SUM(Cost range) / SUM(Clicks range)
CPM total:  =SUM(Cost range) / SUM(Impressions range) * 1000

Point each range at the base-metric columns in your sheet. The same approach works in Microsoft Excel.

What About Reach and Frequency?

Reach and Frequency need extra care, because they cannot be totaled at all, even with the formulas above. Reach counts unique people, and the same person can appear across multiple days, campaigns, or ad sets, so any sum of Reach double-counts them. Frequency is Impressions ÷ Reach, so it inherits the same problem.

Compare Reach and Frequency only within the exact date range and level the platform reports them at. For a fuller explanation and a warehousing strategy, see How to Accurately Warehouse Reach and Frequency Data in BigQuery.

Good to Know

• This applies to every ratio metric, including CPM, CTR, CPC, ROAS, and conversion rate.
• It applies to every ad platform and every reporting tool; the same math fixes it in Power BI, Data Studio, Google Sheets, Excel, and beyond.
• It is not a data issue. The values PMA delivers match the platform row for row; only the destination tool's totals-row aggregation needs adjusting.

For additional assistance, please contact our support team.