Table with multiple expression and two columns of data

[#1]

Hi community,

I am calculating two columns of data 5 different business areas. However, every single value displayed in both columns is the result of a unique calculation, as the data is drawn from various external tables.

Obviously i can display all the expressions in one column easily enough, but how can I designate certain expressions to appear in column A and others in column B when there are no directly common fields in the underlying data.

Category	revenue (column A)	% (column B)
A	£1,232,232	5.31
B	£676,343	6.47
C	£52,356	3.19
D	£8,632,754	7.27
E	£6,754	6.61

[#2]

Do you need unique expressions for each Category, or simple 1 expression for revenue and 2'nd one for %?

regards

Darek

[#3]

A unique expression for each category. So there would be 10 unique expressions in the table above

[#4]

You may like sth like this.

Let ma know if it helps.

regards

Darek

[#5]

Are you essentially saying I need to build a table in the background with the expressions? And then reference that table? I'm not sure how this would work seeing as the calculated values must be dynamic and change when the data range is altered

[#6]

Alex,

please give me some more clear example what you need. Maybe show me fex cases in excel....

In my exaple there is simple way how you may have different expressions for 1 KPI for each category.

But i'am sitll not sure if you really need this.....

regards

Darek

[#7]

OK, i think i know what you mean.

However, you have defined a category table. If I am to use this method, do I need to define such as table as well?

[#8]

Alex,

it is rather no common need, that you want different formulas for each dimension value.

Considering, that you have fact table for example with your sales and category is for example you product category, you should have the same formula for each category. To achieve this you have to do some data transformations in your load script.

Of course, there may be cases, when your "category" is rather KPI name, than business dimension, and then you may need to use different formulas for each of them.

If you have this case, you need pick(nbr,expr_1,expr_2,....,expr_n) eventually if(category='A',sum(value),avg(value)). But personally i prefer pick()

If you want, you may give me more detailed info why you need those different formulas and i will try to reccomend good solution for you.

regards

Darek