2 Replies Latest reply on Jun 20, 2012 11:10 PM by Grzegorz Mikulski

    Alternative to Excel's sumproduct

    Grzegorz Mikulski
    Grzegorz Mikulski Jun 8, 2012 1:53 AM

      Hello,

       

      Can anyone please help me with finding an alternative to Excel's Sumproduct function?

       

      My database currently looks more or less like this:

      bez tytułu1.bmp

       

      First of all I needed a formula that would calculate total Non-staff costs (total of some of the columns). What I came up with is this:

       

      (Total(SUM([TRAVELS])) + Total(SUM([MARKETING & SALES])) + Total(SUM([OFFICE COSTS])) + Total(SUM([COMMUNICATIONS])) + Total(SUM([DATA PROCESSING])) + Total(SUM([FEES AND CONSULTANCY])) + Total(SUM([OTHER EXPENSES1])) + Total(SUM([DEPRECIATION]))  )

       

      As you see in one column I have PeriodCd - which indicates if this is Actual figure, or Budget figure.

       

      What I would like to do is to modify the formula above, so it calculates for me deviation between Budget and Actuals. In Excel I'd acheive this with SUMPRODUCT, how can I do it in Tableu?

       

      I am able to put this up on a graph (actuals above and budget below), but indeed I would only need one graph to show Deviation figure (Budget minus Actual). Is there a way to calculate this? 

      bez tytułu2.bmp

        • 1. Re: Alternative to Excel's sumproduct
          Jonathan Drummey
          Jonathan Drummey Ambassador Jun 20, 2012 10:18 AM (in response to Grzegorz Mikulski)

          Hi Grzegorz,

           

          There are a couple of ways to do this, here's one method:

           

          1) Create a calculated field for the total of A that is TOTAL(SUM(IF [PeriodCd] = "A" THEN [TRAVELS] + [MARKETING & SALES] + ...))

          2) Create a second calculated field for the total of B that is just like the one in #1, except using "B" in the IF statement

          3. Create a third calculated field for the difference that is [A]-[B]

          4. Use that as a measure in the chart, while removing the PeriodCd from the Rows shelf.

           

          Another method would be to use a table calculation with a LOOKUP(), but that would be more complex and somewhat dependent on your data.

           

          Jonathan

          • 2. Re: Alternative to Excel's sumproduct
            Grzegorz Mikulski
            Grzegorz Mikulski Jun 20, 2012 11:10 PM (in response to Jonathan Drummey)

            Hello,

             

            Thanks - this is indeed what I was looking for. This will really help me a lot.

             

            Regards,

            Grzegorz