How to Round Large Numbers in Looker Studio

Visitors using ad blockers, consent management systems, privacy-focused browsers — all of these technologies contribute to imprecise analytics.

How can you change your reporting to reflect this lack of precision in your data? One small step you can take right now is to start using round numbers in your reporting.

For example, here are 3 possible conversion rates:

• 3.28%
• 3.3%
• 3%

Based on each number, would you actually change your tactics? Is this one conversion rate metric giving you enough information to even make a decision? Unlikely. And since we know that this data is imprecise, let’s not include a decimal at all, much less two decimals. We can easily change this by setting the decimal value for the metric to 0 in Looker Studio.

What about larger numbers that don’t have decimals — how can we round those? We use Looker Studio for our reporting and there is a feature called “Compact Numbers” where a number will be truncated if it’s sufficiently large. Here is an example:

While this is an interesting idea, it isn’t very easy to read, especially in a table like this:

There is a ROUND function in Looker Studio, but it only works with decimal numbers, not whole numbers.

We can force the ROUND function to round our metrics by temporarily turning our whole numbers into decimal numbers, like so:

(ROUND((Sessions/100),0))*100

This calculated field is taking our metric (Sessions), dividing it by 100 to turn it into a decimal, rounding that value, and then multiplying it by 100 to return it back to a whole number.

Let’s compare how that looks in a tabular format:

Much easier to read!

That calculation rounds to the nearest hundred, but if you wanted to round to the nearest ten, use this:

(ROUND((Sessions/10),0))*10

Or, to round to the nearest thousand, use this:

(ROUND((Sessions/1000),0))*1000

This rounding can break down a bit when you have a table with larger numbers and smaller numbers in it, like so:

We wouldn’t want to lose those Organic Video sessions, and the other smaller numbers have changed too much via rounding.

In these situations, we can add a CASE function to ROUND the data differently, depending on how large the number is:

CASE
WHEN Sessions <10 THEN Sessions
WHEN Sessions >=10 AND Sessions <1000 THEN ((ROUND((Sessions/10),0))*10)
WHEN Sessions >=1000 AND Sessions <10000 THEN ((ROUND((Sessions/100),0))*100)
WHEN Sessions >= 10000 THEN ((ROUND((Sessions/1000),0))*1000)
END

Using that, we end up with smarter rounding results:

Try these out in your own reports — it should help you avoid the “why don’t these numbers match up?” discussion and instead help you focus on making decisions driven by the data.

Want to know what you can do to report effectively with inaccurate data? Check out our resource, Marketing Analytics Data is Wrong. Can It Be Fixed?!