How to Filter Between Components Without Changing the Denominator in % Contribution Calculations?

Looker Q&A Looker
, ,
1

Hi everyone,

I’m building a dashboard that compares two different product flows (Old vs New).
Each record contains:

  • a total price

  • several component prices (e.g., Feature A, Feature B, Feature C)

  • a flow indicator (Old vs New)

I want to calculate how much each component contributes to the total price, for example:

component_contribution = component_price / total_price

To make the dashboard interactive, I’d like to add a filter at the top that lets the user choose which component(s) they want to explore e.g., Feature A, Feature B, Feature C.

When a user filters the dashboard (e.g., selects Feature A only), the denominator (total price) also gets filtered, which changes the contribution percentage.

For example:

  • Total price for all components = 100

  • Feature A price = 20 → Contribution = 20%

But if the user filters the dashboard to Feature A, the total price becomes 20 as well, so the % suddenly becomes 100% which is not meaningful.

Is there a way in Looker to filter which component is displayed, without filtering the underlying rows used to calculate the denominator?

In other words, I want a filter that changes which component contribution metric is shown, but does not restrict the rows used to compute the total price.

Any help would be greatly appreciated!

2

Hey,

the trick is to get the filtering out of your WHERE clause by creating a parameter which is later used in your measure.

So a) create the parameter containing your products

parameter: component_selector {
  type: unquoted
  allowed_value: { label: "Feature A" value: "feature_a" }
  allowed_value: { label: "Feature B" value: "feature_b" }
  allowed_value: { label: "Feature C" value: "feature_c" }
}

b) create a dynamic measure that checks this parameter and uses afterwards only the prices of selected product. Alternatively you can first built a two step approach for more reusability and create a hidden dimension first and later build the measure on top of it

measure: dynamic_component_price {
  type: sum
  sql: 
    {% if component_selector._parameter_value == 'feature_a' %} ${feature_a_price}
    {% elsif component_selector._parameter_value == 'feature_b' %} ${feature_b_price}
    {% elsif component_selector._parameter_value == 'feature_c' %} ${feature_c_price}
    {% else %} 0 {% endif %} ;;
}

dimension: dynamic_component_dimension {
  hidden: yes
  type: number
  sql: 
    {% if component_selector._parameter_value == 'feature_a' %} ${feature_a_price}
    {% elsif component_selector._parameter_value == 'feature_b' %} ${feature_b_price}
    {% elsif component_selector._parameter_value == 'feature_c' %} ${feature_c_price}
    {% else %} 0 {% endif %} ;;
}

measure: dynamic_component_price {
  type: sum
  sql: ${dynamic_component_dimension} ;;
}

c) create an static total and use it for the calculation

measure: total_price_measure {
  type: sum
  sql: ${total_price} ;;
}

measure: component_contribution_pct {
  type: number
  value_format_name: percent_2
  sql: 1.0 * ${dynamic_component_price} / NULLIF(${total_price_measure}, 0) ;;
}

Now you can use the component_selector parameter on your dashboard and the component_contribution_pct as measure. Ideally, you can add an ‘All Products’ parameter value as the default so that you also address a default setting.

This should solve your problem or at least help you to achieve your final target picture on your dashboard.

Best

1 Like