5 Replies Latest reply on Jan 20, 2014 3:45 AM by Manas Ranjan Kar

# How to make a table comparing a result with multiple target values?

Hi all,

I am trying to make a simple (text) table to show how a set of measures (e.g. sales, costs, promotions) compares to multiple targets set.

The first column should contain the actual result gained, and the next 3 should be that result as a percentage difference of target 1, target 2 and target 3 respectively. It would look like this:

I am looking for advice as to the best way to do that in Tableau. It seems an obvious request but the only reliable way I found so far is to create 4 different reports, one for each column, and glue them together in a dashboard to make them look a bit like they are the same table! I’m sure this cannot be the best way though, and it also falls apart when the dashboard is resized…what I really want is 1 single report, or as few as possible.

It seems that if I wanted to compare all the targets to the actual that would be easy, a % difference table calculation derived from type = “actual”. But how to do it the other way around, expressing the actual as a %difference from the targets?

The source data looks like this:

Attached a workbook with the data embedded in.

Thanks for any assistance.

• ###### 1. Re: How to make a table comparing a result with multiple target values?

Hi, I have used the LOOKUP formula to achieve what you wanted. They have been computed using Type.

The formula, for example for Sales is

(LOOKUP(SUM([Sales]),FIRST())-LOOKUP(SUM([Sales]),0)) /LOOKUP(SUM([Sales]),0)

This formula has been repeated for Costs and Promotions as well.

Cheers !!

1 of 1 people found this helpful
• ###### 2. Re: Re: How to make a table comparing a result with multiple target values?

Hi!

Thanks so much for your quick help! That gets me extremely close to precisely what I was after, very clever :-)

With a modification to the formula I almost managed to get it to show the output exactly I made in the top of my original post, i.e. 3 rows by 4 columns. My only remaining problem is that I wanted to show the "actual" result as a normal number as it's a concrete figure, and the comparisons as % differences. The numbers are perfect, thanks to your formula, but I could not find a way to stop the first result showing as a %.

If we compare my mockup:

to Tableau's

it hopefully shows the issue. Do you have any ideas for that last remaining issue? In any case, thank you so much for your help on the main calculations!

• ###### 3. Re: Re: Re: How to make a table comparing a result with multiple target values?

Hi, glad that worked ! You just need to make a simple modification to the formula

Earlier:

(LOOKUP(SUM([Sales]),FIRST()) - LOOKUP(SUM([Sales]),0))
/ LOOKUP (SUM([Sales]),0)

Now:

IF INDEX()==1 THEN SUM([Sales]) ELSE
(((LOOKUP(SUM([Sales]),FIRST())-LOOKUP(SUM([Sales]),0))
/LOOKUP(SUM([Sales]),0))*100)
END

Since you want percentage and absolute numbers in the same row but in a different format, I needed to do a bit of jiggle with the Type aliases. This is the closest that I could get to your format.

Cheers !!

• ###### 4. Re: Re: Re: How to make a table comparing a result with multiple target values?

Hi,

That's awesome, thanks so much for your help!

• ###### 5. Re: How to make a table comparing a result with multiple target values?

Glad to see that it helped ! Cheers !