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

# Alternative to Excel's sumproduct

Hello,

My database currently looks more or less like this:

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?

• ###### 1. Re: Alternative to Excel's sumproduct

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

Hello,

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

Regards,

Grzegorz