Percent of Total Ignoring Filters

Looker Q&A Looker
, , ,
1

What is the best method to find the percent of total sales while ignoring all filters except one (the date)?

For example: We sell 100 products. I want to plot only 5 products on a line chart by month vs percent of total sales (the total of all 100 products).

Also, we need to plot a selection of product category percent of total sales while only showing one or two category names.

The formulas in this documentation do not address that.

2

Sounds like you could make a derived table to calculate the total that you’ll use in the percent of total calculation, and use bind_filters: to limit the filters that are applied to that derived table.

Here’s an example using dummy data:

view: total_profit_dt {

  derived_table: {
    explore_source: order_items {

      column: profit_per_category { field: order_items.profit_total }

      column: category { field: products.category }

      # This will return total profit across all categories by running a window function
      derived_column: total_profit {
        sql: SUM(profit_per_category) OVER() ;;
      }

      # This will only carry through the filters that you tell it to, you can have as many 
bind_filters: params as you need.

      bind_filters: {
        from_field: order_items.created_date 
        to_field: order_items.created_date
      }
    }
  }

Then, you can make the percent_of_total calculation within this derived table view based on the numbers that are pulled into the derived table (only affected by the date filter):

  dimension: profit_per_category {
    label: "Order Items Total Profit"
    description: "
    Item's sale price minus its cost.
    "
    value_format: "$#,##0.00"
    type: number
  }

  dimension: category {}

  dimension: total_profit {
    type: number
    value_format: "$#,##0.00"
  }

  measure: percent_of_total {
    type: number
    sql: SUM(profit_per_category) / SUM(total_profit) ;;
    value_format_name: percent_1
  }
}

And lastly, you’d join the derived table back to the original explore on category, and only pull in the field you need, percent_of_total:

# Using a refinement here, but you can add to the original explore
# Like you normally would, if you prefer!

explore: +order_items {
  join: total_profit_dt {
    sql_on: ${products.category} = ${total_profit_dt.category} ;;
    relationship: many_to_one
    type: left_outer
    fields: [total_profit_dt.percent_of_total]
  }
}

Now, the date field affects the total_profit and percent_of_total calculations, but the category filter doesn’t affect them (I added the Total Profit field in for demo outside of the code I’m sharing, but you probably wouldn’t want this in production):

Full code for reference:

include: "/_layers/_basic.layer"

view: total_profit_dt {
  derived_table: {
    explore_source: order_items {
      column: profit_per_category { field: order_items.profit_total }
      column: category { field: products.category }

      # This will return total profit across all categories by running a window function
      derived_column: total_profit {
        sql: SUM(profit_per_category) OVER() ;;
      }

      # This will only carry through the filters that you tell it to, you can have as many bind_filters: params as you need.
      bind_filters: {
        from_field: order_items.created_date 
        to_field: order_items.created_date
      }
    }
  }

  dimension: profit_per_category {
    label: "Order Items Total Profit"
    description: "
    Item's sale price minus its cost.
    "
    value_format: "$#,##0.00"
    type: number
  }

  dimension: category {}

  dimension: total_profit {
    type: number
    value_format: "$#,##0.00"
  }

  measure: percent_of_total {
    type: number
    sql: SUM(profit_per_category) / SUM(total_profit) ;;
    value_format_name: percent_1
  }
}

explore: +order_items {
  join: total_profit_dt {
    sql_on: ${products.category} = ${total_profit_dt.category} ;;
    relationship: many_to_one
    type: left_outer
    fields: [total_profit_dt.total_profit, total_profit_dt.percent_of_total]
  }
}
1 Like
3

Thank you very much for the detailed response. That was a huge amount of effort and I appreciate it.

However, I found that it is easier and far better for database performance to (1) add a bit flag in the dimension table coupled with a (2) yesno measure to the LookML view and (3) create a table calculation that “Hides ‘No’s’ from Visualization”.

The table calculation is

${shareofsales.total_sales}/sum(${shareofsales.total_sales})

1 Like