3 Replies Latest reply on Jan 11, 2019 4:45 PM by Ganapathy Palanimuthu

# Exclude specific string values from AVG LOD

The dataset that I am working has a similar structure to the table below. I have an AVG LOD...

{fixed  [Metrics], [School Year]: AVG([Percentage])}

...but I need to exclude School Code A and B from the average calculation.

How can I do it? Thank you in advance.

 School Code Metrics School Year Percentage A Metrics 1 2018 25 B Metrics 1 2018 45 C Metrics 1 2018 21 D Metrics 1 2018 78 E Metrics 1 2018 99 F Metrics 1 2018 58 G Metrics 1 2018 45 H Metrics 1 2018 78 I Metrics 1 2018 36 J Metrics 1 2018 58 A Metrics 2 2018 48 B Metrics 2 2018 22 C Metrics 2 2018 29 D Metrics 2 2018 56 E Metrics 2 2018 89 F Metrics 2 2018 65 G Metrics 2 2018 45 H Metrics 2 2018 75 I Metrics 2 2018 85 J Metrics 2 2018 65 A Metrics 1 2019 32 B Metrics 1 2019 65 C Metrics 1 2019 95 D Metrics 1 2019 45 E Metrics 1 2019 21 F Metrics 1 2019 12 G Metrics 1 2019 99 H Metrics 1 2019 58 I Metrics 1 2019 45 J Metrics 1 2019 78 A Metrics 2 2019 36 B Metrics 2 2019 58 C Metrics 2 2019 48 D Metrics 2 2019 22 E Metrics 2 2019 99 F Metrics 2 2019 58 G Metrics 2 2019 45 H Metrics 2 2019 78 I Metrics 2 2019 36 J Metrics 2 2019 58
• ###### 1. Re: Exclude specific string values from AVG LOD

{fixed  [Metrics], [School Year]: AVG( if [School Code] <> "A" and [School Code] <> "B" then [Percentage] END )}

• ###### 2. Re: Exclude specific string values from AVG LOD

Hi Diogo

This calculation field is the solution to your question.   see the workings below just to reconfirm the results

SUM divided by COUNTD (excluding A and B)

AVG of LOD:

{FIXED   [Metrics], [School Year]: SUM(IF [School Code] = 'A' OR [School Code] = 'B'

THEN 0

ELSE [Percentage]

END)} /

{FIXED   [Metrics], [School Year]: COUNTD(IF [School Code] = 'A' OR [School Code] = 'B'

THEN NULL

ELSE [School Code]

END)}

Working to reconfirm the results

Hope resolved

All the Best

• ###### 3. Re: Exclude specific string values from AVG LOD

attached like has a worksheet with two options

https://public.tableau.com/profile/ganapathy.palanimuthu#!/vizhome/DIOGOBRAGAFORUM12THJAN19/Option2Dashboard?publish=yes

Option 1 - as explained in my first reply - limitation is that you will hard code those two schools to exclude, but if you want to keep it dynamic to exclude and include consider option 2

Option 2  has two sheets, option 2 table sheet and clear filter sheet; you need to add a calculated Field called clear selection with value as blank

Thanks to Russ Lyman, I took some help from his posting for "clear filter" action

###### 1. RE: HOW TO CLEAR A FILTER ACTION ON A PREVIOUS DASHBOARD

All the Best