0

Syntax of field "Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is" is based on choosing a relevant field, then using the syntax to filter it. But calculated fields are not selectable.

So if your pivot is:

Name SUM of number1 SUM of number2 Calculated field (=number1/number2)
Joe 5 10 0.5
Steve 6 8 0.75
Deb 7 20 0.35

How do you filter anything above 0.4?

Name SUM of number1 SUM of number2 Calculated field of number1/2 (filtered)
Joe 5 10 0.5
Steve 6 8 0.75

Calculated field isn't in Pivot filters

1
  • 1
    Which formula is that, number1/number2? Okay, I've added it plus a screenshot. It's a very simple formula, the question is just how to filter the results of this basic formula.
    – LWC
    Jul 9, 2023 at 23:20

1 Answer 1

0

The simplest thing to do might just be to create a filter 'on top' of the pivot table calculated field range and use 'Filter by condition'/'Less than' with 0.4 as the criteria. Alternatively, you could use a QUERY function instead of a pivot table to generate the required result formulaically (possibly nesting one QUERY inside another, depending on the nature of the input data).

1
  • 1
    Surprisingly, Sheets does allow an "on top" regular filter whereas it's blocked in Excel. But unlike Excel, Sheets' pivots can't be flattened (i.e. Report Layout's Show in Tabular Form + Repeat All Item Labels) and without flattening it behaves weirdly (could cut off in the middle of the hierarchy). As for Query, it'll be hard to maintain as Pivot's filters often need to be dynamically and interactively changed, whereas in Query it'll mean constantly changing the formula, which you especially don't want if you share the sheet with others.
    – LWC
    Jul 9, 2023 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.