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

    Calculating Conditional Average excluding NULLs

    Henry H

      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!