Create Custom Table with each row as a different expression

Anonymous — Thu, 17 Aug 2017 15:22:06 GMT

Required output:

Metric	Actual YTD	Target	%ofTarget	Prior Year	%of Prior Year
Open Sales (in $000s)	1000	N/A	N/A	N/A	N/A
Won Sales(in $000s)	2500	N/A	N/A	N/A	N/A
Average Rate (in $)	100	N/A
Expenses (in $)	100	300	0.33%	200	0.45%
Hours Worked (in Hrs)	500	750	0.66%	800	0.625%

I need to produce a table which contains different metrics and each metric might present numbers in a different unit. Any help is appreciated.

Re: Create Custom Table with each row as a different expression

felipedl — Thu, 17 Aug 2017 17:07:59 GMT

Hi Abhay,

Do the following:

For dimension, use the expression:

=ValueList('Open Sales (in $000s)','Won Sales(in $000s)','Average Rate (in $)','Expenses (in $)','Hours Worked (in Hrs)')

And for each measure in 'Actual YTD', 'Target', '% Target', 'Prior Year' '%of Prior Year' use:

pick

(

match

(

ValueList('Open Sales (in $000s)','Won Sales(in $000s)','Average Rate (in $)','Expenses (in $)','Hours Worked (in Hrs)'),

'Open Sales (in $000s)','Won Sales(in $000s)','Average Rate (in $)','Expenses (in $)','Hours Worked (in Hrs)'

1, // put expression here

2, // put expression here

3, // put expression here

4, // put expression here

5 // put expression here

)

So when you change the expressions 1,2,3,4,5 for what you want, each measure will have a different expression, computing different things.

Felipe.