Pivot table, with calculated column

[#1]

Hi,

This is an actualization of a previous post, Thanks to Gysbert Wassenaar to solve one of my problems.

I have this entry data

Month	DataType	Value1	Value2
01	R	10	1
01	O	12	1
02	R	11	1
02	O	15	2

and I would like to obtain this result

Month	Total			02			01
DataType	R	O	%	R	O	%	R	O	%
Value1	21	27	77,78%	11	15	73,33%	10	12	83,33%
Value2	2	3	66,67%	1	2	50,00%	1	1	100,00%

where the rows Value1 and Value2 are expresions and the colum % is an aditional value of the dimension DataType

Value1 = sum(Value1)                    in % column sum({<DataType={'R'}>} Value1) / sum({<DataType={'O'}>} Value1)

Value2 = sum(Value2)                    in % column sum({<DataType={'R'}>} Value2) / sum({<DataType={'O'}>} Value2)

but the result obtained is this

Month	Total		02		01
DataType	R	O	R	O	R	O
Value1	21	27	11	15	10	12
Value2	2	3	1	2	1	1

Any suggestions?

[Gysbert_Wassenaar]

See attached example.

[#3]

Gysbert, Thanks for this solution. It's working fine, but the number of records generated is too high. We are working with millions of records (and Value1, Value2 are really Value1, Value2 .. Value60) and inplementing this solution, the application performance is greatly deteriorated. Is there any other way to get the desired result without greatly increasing the number of records?

[Gysbert_Wassenaar · Accepted solution]

See attached example. No idea how this holds up under millions of records. You may have to resort to creating summary tables in the script.

[#5]

Gysbert, Thanks for this solution. It's working fine.