Data Studio
2K members online now
2K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Aggregations over Null Values

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I've been trying to SUM and COUNT over a list of  Number values that includes Null values, but the responses I am getting do not match what I expect. I expect that SUM and COUNT skip Null values, is this correct?

Re: Aggregations over Null Values

[ Edited ]
Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

My mistake. My problem was not realizing that 'Metrics' on a 'scorecard' are limited to the scope of the values used in the metric. For example, say I had a BigQuery table like so:

 

NameAge
Alice20
Bob40
Carol40

 

If we wanted to show the average age of the people in the table in Datastudio, we might set the table as our datasource and then create a new Metric called Average Age where Average Age = AVG(Age)

 

However, this will give us an average age of 30 rather than 33. This is because Datastudio is grouping the ages prior to averaging them. So rather than taking (20+40+40)/3 = 33.3, it is returning (20+40)/2 = 30.

 

Does anyone know any good workarounds for this? It is a little annoying. 

Aggregations over Null Values

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Potentially a hacky fix, but including a filter on the scorecard for the field you actually want the data grouped by should solve this problem. For instance in the case above I could include a filter that makes sure that 'Name' is not equal to an empty string.

If anyone knows better solutions, please reply to this thread!

In the meantime, this me: https://www.youtube.com/watch?v=iqj6vqFSpt0