*star_border*

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Printer Friendly Page

# Aggregation of a formula is done element wise

[ Edited ]April 2017 - last edited April 2017

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Email to a Friend
- Report Abuse

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?