Solved

# Percent of Total Ignoring Filters

• 2 replies
• 96 views

• New Member

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.

icon

Best answer by kim 1 July 2021, 18:41

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})

View original

### 2 replies

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]  }}``

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})