
1. Re: How to make a table comparing a result with multiple target values?
Manas Ranjan Kar Jan 18, 2014 1:20 AM (in response to adam.medcalf)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 !!

Data table_Solved.twbx 12.2 KB


2. Re: Re: How to make a table comparing a result with multiple target values?
adam.medcalf Jan 17, 2014 8:16 AM (in response to Manas Ranjan Kar)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!

Data table_Solved (2).twbx 12.1 KB


3. Re: Re: Re: How to make a table comparing a result with multiple target values?
Manas Ranjan Kar Jan 18, 2014 1:21 AM (in response to adam.medcalf)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 !!

Data table_Solved _v1.twbx 17.3 KB


4. Re: Re: Re: How to make a table comparing a result with multiple target values?
adam.medcalf Jan 20, 2014 1:26 AM (in response to Manas Ranjan Kar)Hi,
That's awesome, thanks so much for your help!

5. Re: How to make a table comparing a result with multiple target values?
Manas Ranjan Kar Jan 20, 2014 3:45 AM (in response to adam.medcalf)Glad to see that it helped ! Cheers !