Make Measure Value Fixed Regardless of Dimension in the Visual

fxs7576
Participant I

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]:

f7c5d47c-e614-4828-8001-4f2e925a4ee4.jpg

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?

0fb5e998-7412-4c6c-b32a-4619b9369771.jpg
dfb60b6d-89ba-4089-a648-6b77a4336409.jpg

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

0 2 2,120
2 REPLIES 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

fxs7576
Participant I

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

Top Labels in this Space
Top Solution Authors