Question

# Measure outside of the pivot

• 4 replies
• 1389 views

Userlevel 7
+1
• Good Looker
• 829 replies

I hope the topic fits in the table calculations category.

To make the example simple imagine I have a table of number of sessions by URL and then I add a pivot for a boolean flag.

How do I create a measure that only shows once as a difference of the first from the second (or the other way)?

Pivot_offset would give me a custom measure, of course, but it will show twice in the result table with the first (or second) column empty.

Any ideas?

### 4 replies

Userlevel 3

Hi,

Yes table calculations seems appropriate here, although you could also easily achieve this in the model.

If you want to use a table calculation then look at the `pivot_where` function, https://docs.looker.com/exploring-data/creating-looker-expressions/pivot_where. You can use this to create one measure, rather than a measure per pivot.

The syntax would be

`pivot_where(\${dimension}=yes,\${measure_1}) - pivot_where(\${dimension}=no,\${measure_2})`

Andy

Userlevel 7
+1

Oh! That’s an interesting approach. Thanks, I will give it a go and report back!

I checked and even by entering this simple formula:

`pivot_where(\${beforeafter}="1. After", \${measure})`

it says:

`The first argument for "pivot_where" must be a measure or a pivoted dimension`

That before/after is custom dimension (string)

– another edit

When I saved it, it still worked but it was still showing the above error. Perhaps it has to do something with the fact that I used custom dimension here?

Userlevel 3

I’ve checked and I see the same error message, though you’re right that it does work in the end. It looks like its caused by the pivot dimension being a custom dimension rather than an actual dimension. Looks like a bug to me.

Andy

Userlevel 7
+1

Yes, good to know that it’s sometimes good to click SAVE anyway to see if it works, in case error is a false positive 🙂