Question

# Subtracting Measures from Dimensions and vice versa

• 651 views

• Member
• 37 replies

First of all, this is not a valid operation in the SQL/LookML world. Just as it is NOT possible to perform

``````SELECT
(SUM(order_items.amount) - product.cost) AS order_profit
FROM order_items
LEFT JOIN order_items.product_id = product.id
``````

But the end result is possible. Let’s walk through a few examples:

###1) Measure::Measure operation

Let’s assume we have the following views and dimensions/measures defined:

``````- view: orders
fields:

- dimension: id
primary_key: true

- dimension: order_price
type: number

- view: product
fields:
- dimension: product_id
primary_key: true

- dimension: product_cost

- view: order_items
fields:
- dimension: order_items_id
primary_key: true

- dimension: order_id

- measure: total_product_cost
type: sum
sql: \${product.product_cost}

- dimension: product_id
``````

``````- explore: order_items
joins:
- join: orders
foreign_key: order_items.order_id

- join: product
foreign_key: order_items.product_id
``````

And our objective is to calculate the profit on each order. Normally, we would have `total_item_price` measure in the order_items view from which we would subtract the `total_product_cost` measure.

However, frequently we have other order-specific fees associated with every order that force us to make use of the `order_price` dimension value that is captured within the orders table - not within an order items table.

Since we cannot subtract a measure (`order_items.total_product_cost`) from a dimension (`orders.order_price`), we create two new measures:

``````- view: orders
fields:
- measure: total_order_price
type: sum
sql: \${order_price}

- measure: total_order_profit
type: number
sql: \${total_order_price} - \${order_items.total_product_cost}
``````

In this case, both `total_order_price` and `order_items.total_product_cost` are measures that are calculated over unique orders and corresponding unique items. Even though any query that pulls `total_order_profit` will fan-out both orders and product tables, we have ensured that product_cost is correctly duplicated across all items and is aggregated at the item level, while orders are aggregated only at the order level (e.g. `total_order_price = order_price` for every unique order_id record).

###2) Dimension::Dimension operation

Another solution is to convert a measure to a dimension by creating a fact table (aka derived table).

``````- view: order_item_facts
derived_table:
sql: |
SELECT
order_items.order_id
, SUM(product_cost) AS order_cost
FROM order_items
LEFT JOIN product ON order_items.product_id  = product.product_id
GROUP BY 1
fields:
- dimension: order_id
primary_key: true

- measure: order_cost
type: number
``````

And the associated change in the explore:

``````- explore: order_items
joins:
- join: orders
foreign_key: order_items.order_id

- join: product
foreign_key: order_items.product_id

- join: order_item_facts
view_label: 'Orders' #syntax available starting 3.20
foreign_key: order_items.order_id
``````

Now the operation we are looking for happens at order level as expected:

``````- view: orders
fields:
- dimension: order_profit
type: number
sql: \${order_price} - \${order_item_facts.order_cost}
``````

Userlevel 6 Nice explanation.

A note:

So this pattern can get you into trouble. You need to be very careful when computing sums from dimensions that aren’t in the same view. It this case, each order_item is joined through a foreign key to products so the sum computes correctly, but as a rule, I avoid this type of computation. If dimension varies on the primary key of the view the computation will overflow and report an error.

For example, the following sum won’t work because for a given order_id, there can different order_items.amount.

``````- view: orders
fields;
- measure: total_order_amount
type: sum
sql: \${order_items.amount}
``````

Sometimes its hard to figure out when you can and can’t sum across views, so as a rule, I just don’t do it.