Put a calculation both in rows and columns

[#1]

Hi there,

I'm looking to present some financial key figures in a small pivot table with a calculated field both in row and column:

example

Actual Plan Variance

Revenue

Profit

Profitmargin%

Revenue and profit are values of a field called 'reportinglevel1),

Actual an plan are based on field called 'plan/actual indicator).

Variance is Actual - plan

Profitmargin% should be Profit / Revenue

Seems like a vary simple taks but i don't seem to be able to put calculations in both rows an columns.

Has anyone come across this problem and found a solution?

Thanks,

Wim

[#2]

Not sure how this will work out but you would need to add in Revenue, Profit & ProfitMargin% as calculated dimensions. Then add Actual, Plan & Variance as expressions.

Try it & let me know how it goes.

[johnw]

Attached is one way to do it. Seems like it should be simple, but... no so much. The example doesn't use your fields. It's just an example I had lying around that demonstrates the technique.

[#4]

Thanks,

Never used the 'pick' function before. It seems to me that the example only works if you use numbers (1,2,3..) in the value list. I f you were to use descriptive values like 'Revenue, Profit, ProfitMargin' then you should add If statements to determine the number within the value list, right?

[johnw]

You can still use pick, but you have to do pick(match()). And IF statements work too. Not sure which I would prefer at this point. Pick(match()) probably runs slightly faster, and uses less code. But IF statements are probably more clear, and therefore more maintainable. I've updated the example with both approaches.