Average Formula to Exclude blank or zero values

[HeathBiller]

Hi,

I am trying to average a list of Ages but want to exclude any cells that are blank or zero. Here is my current formula.      average(Client[Client Age])

I have tried multiple different ways to try and exclude blank and zero values but so far have been unsuccessful. Hoping someone might have some ideas. 

Thank you.

[Suvrutt_Gurjar]

Please create a slice called say "Valid_Ages" on the table with a row filter expression something like

AND( [Client Age]>0, ISNOTBLANK([Client Age]))

AVERAGE( Valid_Ages[Client Age])

[HeathBiller]

Thank you! I am assuming that would work but I already have a few graphs pulling from the current data slice that I have so I used AleksiAlkio's formula above to keep it a little more streamlined.

[Suvrutt_Gurjar]

One thought behind suggesting a slice was the multirow expression such as SliceName[ColumnName]  processes fewer records as slice has  already selected the desired records. 

[HeathBiller]

So doing more things via slices versus multirow expressions will help the app load faster is what you are saying?

[Suvrutt_Gurjar]

In general, I repeat in general, I believe yes.

An app sync performance is complex phenomenon where multiple factors play a role- main issues being created by virtual columns that compute multirow expressions over high number of records in a table, loading a very large table, the AppSheet plan ( which decides degree of parallelism)  and so on. AppSheet performance analyzer does not specifically mention  compute time of a slice.

However, I believe a slice + multirow expression on the lesser number of rows filtered by slice is generally better than a pure multirow expression on higher number of rows. So as a thumb rule , I may use that approach because I believe it may not be at least worse than pure multirow expression. However exact benefit will depend on many factors. For example , if the slice in your case filters out only 10 rows out of total 1000, the benefits may be negligible.

If you search the community , you will come across multiple posts on multiple aspects of an app performance. The below mentioned posts specifically discuss slices that you may want to take a look.

The below guidance is incidentally by senior community colleague @AleksiAlkio . The guidance is though mainly about use of multirow expression in a VC.

Performance questions - Google Cloud Community

Solved: sync - Google Cloud Community

Slice versus Virtual Column Performance - Google Cloud Community

I am sure other community members will share their insights as well.

[AleksiAlkio]

In this case the best approach is difficult to say as we don't know all the details.  Like where is this calculation used. Is it on a template, in a normal or virtual column, with the initial value etc. This matters as well.

[Suvrutt_Gurjar]

Thank you for your guidance Aleksi. 

In this regard, one more observation about multirow expressions in expression assistant is as follows.

The expression assistant also warns about sync performance when a SELECT()  with arguments is used. The following example is from a real column.

But expression assistant does not warn about sync performance when a simple slice or even table based multirow expression is used without any arguments. The below expression in the same real column is based on a slice where 

The slice "Specific_Match_Round" has the following expression

May I request your insights on this warning behavior of the expression assistant?

Maybe this is just an error in the expression assistant or expression assistant on purpose does not warn about simpler multirow expressions?

[AleksiAlkio · Accepted solution]

Another way is to use SELECT() with your formula, like..
AVERAGE(SELECT(Client[Client Age],AND(ISNOTBLANK([Client Age]),[Client Age]>0)))

[HeathBiller]

Thank you that worked out well. I appreciate the help.