11 Replies Latest reply on Sep 24, 2018 3:29 PM by Boreak Silk

    Average with NULL

    Boreak Silk

      Hi Tableau Users,

       

      I'm trying to compare sales for the past 12 months to average sales by month and category. I came up with below formula

      to calculate average:

       

      { FIXED DATEPART('month',[Date]), [Category] :

      AVG({ FIXED DATEPART('year',[Date]), DATEPART('month',[Date]), [Category]:

      SUM([Value])})}

       

      it does not work correctly because it does not count months when no sales (NULL), which should be zero in my analysis.

      Any advice how to fix this? Attached is my workbook.

       

      Thanks in advance.

       

      Bo