Make Measure Value Fixed Regardless of Dimension in the Visual

Looker Q&A Looker
1

Hello Looker Community,

I’m wondering if there’s a way to make the value of Measure changes due to the Dimensions in the filter, but not those in the visual.

So for example in Explore, I have the following measures named [Total Utilized Item] and [Total Available Item]:

When we add any dimension field (say [Manager] or [Region]), how can we make sure that the value for [Total Available Item] remains the same (400) across different dimension values, and it should only change due to filter?

I’ve looked for many methods in LookML, but to no avail. Thanks much in advance.

2

Hi, Hopefully I understand what you are trying to do here….

The only way I have managed to do something like this is using a merged query.
So I have a query which is
Region | Manager | utilized item

merged to a separate query off the same explore on the region field:
Region | Available Item

then the utilization rate calculated in a table calculation dividing one by the other

Hope this helps?
Gordon

1 Like
3

Hi @Gordon1 , I haven’t tried the ‘merge query’ method, but your ‘Table Calculation’ works.

So here’s what I do:

  1. Drag [manager] (or [region]) dimension and [utilized_item] measure into explore.
  2. Create a Table Calculation named [available_item] with this calculation: sum(${view_name.available_item}). In this example, this gives 400 row-by-row.
  3. Add another Table calculation for computing the [utilization_rate]: ${utilized_item} / ${available_item}

Really appreciate for your help.

-Felix

1 Like