CODEWITHSUNDEEP
looker-studio
J.Doe
J.Doe

Reputation: 131

How can I compare Product Sales to Total Sales?

I have a list of 5 products in my BigQuery data set:

Product_Name Sales
A 1000
B 2000
C 3000
D 4000
E 5000

How can I get %OutOfTotalSales?

Product_Name Sales %OutOfTotalSales
A 1000 6.67%
B 2000 13.33%
C 3000 20.00%
D 4000 26.67%
E 5000 33.33%

I assume that I need to create an extra field, TOTAL_GROSS:

TOTAL_GROSS / PRODUCT_GROSS*100/TOTAL_GROSS

The question would then be: How can I get TOTAL_GROSS inside this expression?

Sample:

Upvotes: 0

Views: 375

Answers (2)

Nimantha
Nimantha

Reputation: 6471

It can be achieved using the comparison metric, Percent of total:

Table

  • Dimension 1: Product_Name
  • Metric 1: Sales
    • Aggregation: SUM
  • Metric 2: Sales
    • Rename: %OutOfTotalSales
    • Aggregation: SUM
    • Comparison calculation: Percent of total (Relative to corresponding data)

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

gif

Upvotes: 1

Bobbylank
Bobbylank

Reputation: 1946

If I understand correctly then ideally you would do that calculation outside of Data Studio as Data Studio isn't really set up to do that automatically.

However if that isn't an option, you'll have to create a metric separately for each product which for product A would look something like:

sum(CASE WHEN Product_Name = 'A' then Sales Else 0 END)/sum(Sales)

To do the calculation in BigQuery you would do:

SELECT Product_Name, Sales, Sales/SUM(Sales) OVER () as %OutOfTotalSales
FROM you_table

Upvotes: 3

Related Questions