Row average in a Cross Table?
I have a preset to give a SUM at the end of each row in a cross table but how can I get an average of the row?
Simpel cross table. Date on horizontal axis, catagories on vertical and values on values axis.
Thank you for taking a look at it. I poked it with a stick for quite a while and got nowhere that wasn't clunly!
It is a bit surprising it isn't an option along with the grand total one!!
Maybe one day.
Thanks for trying!
Oof, I kind of don't think that will be possible in any easy way due to the way the cross table works. It depends so much on the horizontal axis values. Really you need the enhancement request where you can have row totals based on aggregations different than the cell value functions.
Beyond that, the only way I could think to do this would be very cludgy - meaning you would have to calculate those averages somewhere else (data function, IronPython script) then insert the results as new rows in a new display version of the table (original+calculated results). In your [Date] column, you would need to make a new version that was a string and then insert 'Average' for your new rows which had the exact grouping as the cross table (average by number, name, date, etc). Then it would result in a new 'Average' column in your Cross Table. That is sloppy though. Or you could just keep it as a separate visualization...
Yes, The "example average column" would be the last column on the right hand side of the cross table. Same place as it would put the Grand total for Rows column.
The Average would be what ever is in the cross table but not the entire data set. In this example it is 12 months of values in the cross table. It is common for me to limit the data in the cross table to recent vs. the total that might be in an underlying table.
Really appreciate the help on this Sean.
Trying to understand the requirements: So ideally you want the 'Average' column from the bottom visualization in that dxp to be the last column on the top?
Despite it being a simple configuration, it might be better if you provide an example and explain which value you want. For example, you could use an expression like this on the cell values axis to get the average for the row (average of all data points, not average of aggregated results in cross table):
Avg([SALARY_87]) OVER (All([Axis.Columns]))
Can you provide an example dxp?
Any thoughts on this Sean? Thanks.
Sean, I have posted a sample DXP.
I'd like to get an "Averages" column similar to the Grand Total for Rows column option.
It should reflect the average of the data for the row in the cross table. In this case 12 months.
The lower cross table gives the results I would expect.
Thanks Sean, I'll try to get one posted on Monday.