Avg in a cross table

[#1]

Hello,

I have a problem calculating the right averages in a cross table.

I have the following data:

DIM 1 \ DIM 2	X	Y	Total
A	0	1	1
B	1	0	1
Total	1	1	2

I would like to calculate averages based on the total of the whole table (total = 2). So, it must look like:

DIM 1 \ DIM 2	X	Y	Total
A	0 %	50 %	50 %
B	50 %	0 %	50 %
Total	50 %	50 %	100 %

I only get it work based on the row totals.

Searched the other discussions, but still no solution for my problem.

Can anybody help? Thanks!

[v_iyyappan]

Hi ,

     Ignore the Last example . So check the Current attachment for solution.

Regards,

Iyyappan

[#3]

Hi Iyyappan,

Thanks for the fast answer.

However, unfortunatly the file is opened too much times with a personal version of QV, so it cannot be opened.

Can you please send a screenshot of the solution?

Thanks!

[CELAMBARASAN]

Hi,

     For X Expression use X/(Sum(TOTAL X)+Sum(TOTAL Y))

     For Y Expression use Y/(Sum(TOTAL X)+Sum(TOTAL Y))

      For Total Expression use RangeSum(X,Y)/ (Sum(TOTAL X)+Sum(TOTAL Y))

Don't forget check the Show in Percentage option in the number tab of the chart properties.

Hope it helps

Celambarasan

[#5]

Hi,

Please find attached the complete solution for this issue. In the script, use the CROSSTABLE function in order to transform your input data! Do not import the total columns and rows from your input table! Then, you can build straight tables and other objects the usual way in your application. See example attached!

/sebablum

[#6]

Thanks for your reply.

Maybe my first post was not realy clear, my original data is:

Dimension 1	Dimension 2	Amount
A	X	0
B	Y	0
A	Y	1
B	X	1

The first two columns (Dimension 1 and Dimension 2) are codes. The last column is the amount.

After using the pivot table, it is like:

DIM 1 \ DIM 2	X	Y	Total
A	0	1	1
B	1	0	1
Total	1	1	2

Now I am searching for the averages based on the overall total.

Sorry for the misunderstanding.

[#7]

Thats it! Thanks!

[CELAMBARASAN · Accepted solution]

Hi,

     If so then use expression as Sum(Amount)/Sum(Total Amount)

Hope it helps

Celambarasan

[#9]

I am not sure if I understood right, but please find a new example attached. In my opinion, Celambarasan's suggestion would work. I've used set analysis to solve the issue. In order to avoid rangesum, it is possible to create the total column as X+Y.

Solved?

/sebablum