How to show percent of total that recognizes global filter selections

[amekojc]

I'm trying to implement a percent of total measure in pivot table. I succeeded in creating a measure using the below formula.

However, I want this measure to recognize global filter selections. For example, I want to show emissions share by category. But if I remove 1 or 2 values from category global filter, I'd like the total share to recalculate the share with the remaining categories.

Was anyone able to achieve this?

[balajij8]

You can follow below to create a dynamic Percent that responds seamlessly to global filter selections

You can tie Global Filter directly to a Parameter in the Query and it allows recalculating the SUM only for the selected categories.

• Modify Query: Update your dataset query to include a clause with a multi select parameter for categories.

SELECT
  category,
  region,
  emissions,
  -- Percent of column total (by region)
  emissions
    * 1.0
    / SUM(emissions) OVER (PARTITION BY region) AS pct_of_column_total,
  -- Percent of grand total
  emissions
    * 1.0
    / SUM(emissions) OVER () AS pct_of_grand_total
FROM
  emission_data
WHERE
  :category_filter IS NULL
  OR size(:category_filter) = 0
  OR array_contains(:category_filter, category)

• Set up the Global Filter: On Dashboard, add a Filter, set it to Query Parameter and map it to Category_filter

The query fetches the data with only the selected categories and pivot table will perfectly reflect 100% across the remaining items.

[Ashwin_DSA]

Hi @amekojc,

The approach in your screenshot is the fixed LOD pattern, where you first define a total such as SUM(emissions) OVER () and then divide the grouped value by that total. That approach works for a basic per cent-of-total calculation, but it will not automatically recalculate when a regular global field filter removes some category values, because fixed LOD is computed before visualisation groupings and filters are applied. The relevant public doc is Level of detail (LOD) expressions.

If the goal is... show each category’s share of only the categories that are still selected in the dashboard filter.... then the better option in dashboards is to use a coarser LOD expression instead of fixed LOD. Databricks documents coarser LOD specifically for dynamic groupings and filter-aware aggregations, including percent-of-total use cases.

For example, in a dashboard custom calculation, the pattern would look like this..

SUM(emissions) / (
  SUM(emissions) AGGREGATE OVER (PARTITION BY * EXCEPT (category))
)

That tells the denominator to aggregate across the current partition while excluding category, which is what makes the share recalculate over the remaining filtered categories rather than over the original full dataset.

If you want to stay with fixed LOD, the caveat is that dashboard field filters will not affect it. In that case, the supported workaround is to make the filter a parameter that changes the underlying dataset SQL rather than relying on a normal field filter. The same LOD doc above explicitly calls this out.

I also think you can use metric views for this. Metric views do support this kind of logic, but again, the distinction is between fixed LOD and coarser LOD. Fixed LOD in metric views is also computed before query-time filters, so it is not the right choice if you want the denominator to update as the user’s selection changes. The public doc for this is Use level of detail (LOD) expressions in metric views.

For metric views, the filter-aware option is coarser LOD implemented through window measures with range: all. Databricks documents this as the way to support dynamic groupings and query-time filter-aware percent-of-total calculations in metric views. The caveat is that window measures are currently marked experimental. You can see that in Advanced techniques for metric views.

A metric view version would look roughly like this:

measures:
  - name: emissions
    expr: SUM(emissions)

  - name: all_category_emissions
    expr: SUM(emissions)
    window:
      - order: category
        range: all
        semiadditive: last

  - name: emissions_share
    expr: MEASURE(emissions) / MEASURE(all_category_emissions)

This is achievable, but not with a fixed LOD denominator if the category selection is just a regular global field filter. In dashboards, the recommended path is coarser LOD with AGGREGATE OVER (PARTITION BY * EXCEPT (...)), and in metric views the equivalent is coarser LOD via window measures, with the caveat that window measures are still experimental.

Hope this helps.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.