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

    Alternative to Excel's sumproduct

    Grzegorz Mikulski

      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

          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

            Hello,

             

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

             

            Regards,

            Grzegorz