Data Studio
2.3K members online now
2.3K 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?