8 Replies Latest reply: Feb 13, 2012 4:30 AM by Sebastian Blum

# Avg in a cross table

Hello,

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

I have the following data:

DIM 1 \ DIM 2XYTotal
A011
B101
Total112

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

DIM 1 \ DIM 2XYTotal
A0 %50 %50 %
B50 %0 %50 %
Total50 %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!

• ###### Re: Avg in a cross table

Hi ,

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

Regards,

Iyyappan

• ###### Re: Avg in a cross table

Hi Iyyappan,

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!

• ###### Re: Avg in a cross table

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

• ###### Re: Avg in a cross table

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

Dimension 1Dimension 2Amount
A X0
BY0
AY1
BX1

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 2XYTotal
A011
B101
Total112

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

Sorry for the misunderstanding.

• ###### Re: Avg in a cross table

Hi,

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

Hope it helps

Celambarasan

• ###### Re: Avg in a cross table

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

• ###### Re: Avg in a cross table

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

• ###### Re: Avg in a cross table

Thats it! Thanks!