I need to:
Sum the total number of items if they come from the same order and have the same SKU.
I wrote this formula. It’s not working – any suggestions?
IF(COUNT(Order Name)>1 AND COUNT(Lineitem SKU)>1, SUM(Lineitem quantity), 0).
Understand your issue you just have to use the sum method of the metric instead of calculated field for example i created one sheet for the demo:
If you want to sum quantities for matching orders and SKUs, the most efficient method is using Looker’s Metric Aggregation rather than a manual calculated field. Here is the step-by-step process:
-
Prepare your Data: Connect your sheet (containing Order Name, SKU, and Quantity) to Looker Studio.
-
Set your Dimensions: In your table setup, add Order Name and Lineitem SKU as your Dimensions. This tells Looker how to group the items.
-
Configure the Metric: * Click on Add metric in the setup panel.
-
Select Lineitem quantity.
-
Click the Edit icon (pencil) next to the metric and ensure the Aggregation is set to SUM.
-
-
The Result: Looker will automatically identify rows with the same Order and SKU and display their combined total in the table.
This approach is much cleaner than using a CASE statement and ensures your data stays accurate as you add more rows!
