Create Custom Table with each row as a different expression

[Anonymous]

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.

[felipedl]

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.