AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

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

Aggregation of a formula is done element wise

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Have a hypothetical situation: I have an ecommerce database with transactions, where 2 columns are:

- margin: my margin on selling 1 item

- quantity: number of sold item in that transaction

 

I create a calculated metrics in Datastudio:

profit = margin x quantity

 

Then if I see each transaction individually it is all good, I see things like:

acme item 1, quantity: 2, margin: $0.5, profit: $1

acme item 2, quantity: 1, margin: $1, profit: $1

 

If I want to show my aggregate profit: then it should be $2

sum(profit) = sum(margin x quantity) = sum(2x$0.5, 1x$1)  = sum($1,$1) = $2

 

BUT I see a profit of $4.5, since Datastudio seems to do the following calculation:

sum(profit) = sum(margin) x sum(quantity) = sum($0.5,$1) x sum(2,1) = $1.5 x 3 = $4.5

 

So Datastudio does element wise summary then it applies the formula for the element wise summaries, which is clearly wrong. How can I fix this behavior?