Problem with SUM

[sparur]

Hello, guys.

I have some problems with such situation:

I have simple table:

and I want to get sum of Sales and Plan values by Company, like this:

Company, Fields - 2 dimension, Result - expresson.

BUT the main difficulty is that I want to achieve this with one expression!

I created new field with name 'Fields', which contain values with all my Key Figures ('Sales' and 'Plan'),

and I want to use something like this: sum(Fields), but I can't achieve that Fields transform to values,

i.e.
for lines, where Fields = 'Plan' I want get expression - sum(Plan),

for line where Fields = 'Sales' - get sum(Sales)

Can somebody help me?

I know, that can use crosstable function (instead of new field 'Fields') for transforming my source table, but I try to avoid it.

look at my example in attachment.

[sparur]

anyone have any ideas?

[#3]

Crosstable is an elegant solution here. I would use that.

[sparur]

Hello, Matt.

yes, I know that is one of solutions, but I want to avoid it. Because it very "hard" statement. In my real project I have fact table about 5 million of rows and crosstable process take a long time. I want to find more elegant solution(without crosstable), or I want to be sure that crosstable is unique solution.

[srinivasa1]

Even I too feel Cross tabel can helpn this case.

[sparur]

Hello, srinivasa1

I know about crosstable, thanks. but I want to find other solution.