Hello: I have a crosstab report in Cognos 11 that has stores going down and Category groupings going across the top. I need to be able to divide an individual store's sales on a row by the average of the total store sales listed for a grouping.
For example: In the below I need to be able to take store 36 sales, $49,383.26 / average(all of the store sales for this category). In Excel, I get the value under the Index column, which is correct. In Cognos, I am getting .013 instead of 1.11. I have tried various calculations and detail functions i.e. total(sales for [Category) etc. Does anyone have a way to do this they can share with me? Thanks.
Store Sales Total sales Sub Cat Count Index
36 49,383.26 11,462,331.85 0.013 1.11
40 33,928.02 11,462,331.85 0.009 0.30
46 48,624.56 11,462,331.85 0.013 0.25
49 57,854.51 11,462,331.85 0.015 0.22
76 75,611.59 11,462,331.85 0.020 0.26
130 28,390.19 11,462,331.85 0.007 0.36
135 12,756.05 11,462,331.85 0.003 0.23
146 47,972.69 11,462,331.85 0.013 0.54
159 59,728.83 11,462,331.85 0.016 0.15