How to show the total average of filtered dataset on line level (part of dataset) in Cross Table

[Jurjen Steel]

Dear all,

I have a dataset on which I have set some filters. After filtering I create a Cross Table that shows averages on Turnover per Product (see below). The cross table automatically shows the total average of the filtered data on the totals line at the bottum of the table. What I am trying to achieve is to present the total average (15,21 in the example) on each line of the cross table. Until now I haven't worked it out how to do this.

I tried using a calculated column in the dataset which calculates the total average of a sector (using over statements) so that I could use this column in the Cross Table. But this option didn't work too well as my filter settings within the filtered sectorare much more complex that in the example below. So if change a filter I would like to have the total average moving with it.

Could anyone help me out here

 

Many Thanks in advance.

[Khushboo Rabadia]

You can use over function in the custom expression like

Sum([TurnOver]) OVER (All([Axis.Rows])) / Sum([Qty]) OVER (All([Axis.Rows]))

This will sum up turnover over all rows divide it by qty over all rows giving 15.21 in the result.