1 Reply Latest reply on Mar 31, 2016 12:30 PM by Sho Fujiwara

    FINDING AVERAGE BY LOOKUP

    KUSHWANT SINGH

      I am trying to find average using lookup but not able to do so. Also I need to forecast using a basic formula which is not happening in Tableau.

      I can do it easily in Excel but facing issues in Tableau. Please help. I am using the below formula :

       

      IF ISNULL(SUM([Actual])) = TRUE

      THEN LOOKUP(WINDOW_AVG

      ((AVG([Budget]))),-9)

      ELSE

      0

      END

       

        • 1. Re: FINDING AVERAGE BY LOOKUP
          Sho Fujiwara

          Hi Kushwant,

           

          What you want to use is a LoD calculation.

           

          First, create a field (I called it [AvgBudgetPerActual]) that calculates the average [Budget] per [Actual] value.

           

          {FIXED [Actual]:

          AVG(Budget)}

           

          Since Oct,Nov, and Dec have NULL [Actual] values, the [AvgBudgetPerActual] field will be the Average of their [Budget] for these months (1,333).

           

          Now, you want to combine your [Actual] field with [AvgBudgetPerActual] in a new field (I called this [Actual2], where it takes on the value of [AvgBudgetPerActual] when [Actual] is NULL.

           

          if isnull([Actual]) THEN

          [AvgBudgetPerActual] ELSE [Actual]

          END

           

           

          Hope that helps!

           

          Sho