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

Creating a field that shows Quantity x Price totals

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello,

 

I am new to data studio and databases in general. Everything was going great and I was pulling most metrics I wanted until I just wanted to have a metric showing the total gross sales which would show the total of sales, each sale being QUANTITY ORDERED x UNIT PRICE.

 

Problem is Data Studio is multiplying the aggregated quantity by the aggregated unit price. So the end total is not right as the variation of quantity is not reflected. I tried changing aggregation to none but then it did not work.

 

Is there any way to have Data Studio multiply each quantity by its right price then summing instead of summing the totals of each field?

 

Thank you very much and sorry for my English!

 

Best,

Haroun

1 Expert replyverified_user

Creating a field that shows Quantity x Price totals

Top Contributor
# 2
Top Contributor

Try creating a calculated metric in GA first and then use that in your DS data source

VP & Chief Evangelist at Hub'Scan | Contact me
Level 80 Digital Analytics Warrior, KPI Therapist and Keeper of the One True Tagging Plan

Re: Creating a field that shows Quantity x Price totals

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
Hi Julien,
Thanks you. But the source is a MySQL database. I can't create Google analytics metrics.
Best,


Envoyé depuis mon smartphone Samsung Galaxy.

Creating a field that shows Quantity x Price totals

Top Contributor
# 4
Top Contributor

Bonjour Haroun, 

 

in that case, you could create the desired field in MySQL and it would be available in DS

VP & Chief Evangelist at Hub'Scan | Contact me
Level 80 Digital Analytics Warrior, KPI Therapist and Keeper of the One True Tagging Plan

Creating a field that shows Quantity x Price totals

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Bonjour Julien,

 

Thanks for the answer. I was hoping there was a solution built in DS but seems like yes, changing the database is the only option. I hope my friend programmer will know how to handle that as looking online I see some people recommend Calculated Columns, Triggers or Views, and I know nothing about that :-)

 

Best,

Haroun

Creating a field that shows Quantity x Price totals

Top Contributor
# 6
Top Contributor

here is some proto-SQL code your programmer friend can use:

 

UPDATE ecommerce.order_items SET item_total = qty_sold * item_price WHERE 1;

VP & Chief Evangelist at Hub'Scan | Contact me
Level 80 Digital Analytics Warrior, KPI Therapist and Keeper of the One True Tagging Plan

Creating a field that shows Quantity x Price totals

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Thanks. I showed him and the BDD is MySQL. He sees how to update the fields for existing data but is searching how to make sure it calculates all future rows directly.

Marked as Best Answer.
Solution
Accepted by topic author Haroun S
January 2017

Creating a field that shows Quantity x Price totals

Top Contributor
# 8
Top Contributor

He can either set a MySQL query to run as an hourly or daily cron job (he will know what that is) or change the order registration MySQL query to calculate that field as each order is placed.

VP & Chief Evangelist at Hub'Scan | Contact me
Level 80 Digital Analytics Warrior, KPI Therapist and Keeper of the One True Tagging Plan

Creating a field that shows Quantity x Price totals

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Thank you he loved to read your solution and will implement it!