2 Replies Latest reply on Mar 18, 2016 9:45 AM by Henry H

# Calculating Conditional Average excluding NULLs

Hi all,

I'm having trouble showing an averages table on tableau.  I've attached a sample dataset to illustrate what I need.

Let's say I have three columns: an amounts columns with some nulls, a dates column and a "Metric" column.  I need to show the average of the amounts columns by Metric by month.  However, I should only take into account numbers where there is no NULLs for any Metrics column for that date.

So in the attached example, because we have NULL values for 1/8 - 1/15 for Metric A, we should not take into account those dates when we calculate the average for Metric B for the month.  Likewise, because we have NULL values for 1/26 - 1/27 for Metric B, we should not take into account those dates when we calculate the average for Metric A for the month.  In the end, we end up taking the averages for each Metric using only the 1/19 - 1/25 dates.

On the second tab in the excel I've shown how I would like the average table to be shown.

Appreciate the help in advance.  Thanks!

• ###### 1. Re: Calculating Conditional Average excluding NULLs

Tableau will take care of it.

Drag the Metric Value in Text Shelf and select Avg Option. If your data source is Excel then make sure you format data and remove NULL and leave cells blank.

• ###### 2. Re: Calculating Conditional Average excluding NULLs

This will not work.  The problem is that we don't want to include any days in the calculations where there's a null value in any metric.  So if we have an actual value in metric A but a null value in metric B on the same day, the Metric A value should not be included when we calculate the average for Metric A.