I have cross tab report with 4 row and 4column and measures like below.
A b c d Total
Total cost 155 255 300 125 835
cost less fuel 25 75 152 124 376
avg cost 105 234 324 234 897
avg num of days 6 4 5 2 17
for the last row avg num of days I want to get the avg of all the 6+4+5+2 which is 4.25 not 17.
when I tried to add the avg for the avg num of days column it is applying to all other total numbers. How can Iaachive this??
IN theory...
Create a new column "Average" to right of "Total" column
Use the expression to derive the average as you have done
Select the cell at intersection of Total column and Avg Num of Days row edge's (tuple) and set its box type property to None
Select the cells at intersection of Average column and the other 3 row edges, as well as the Average Column's heading and set their box type property to none.
Let us know if this works for you
See this crosstab output:
PRODUCTION_COST GROSS_MARGIN Total
4110 5 0.28 2.64
5110 34.97 0.3 35.27
6110 85.11 0.28 85.39
7110 46.38 0.28 46.66
As u can see for 4110 row the total is actually the average
The total data item is defined as,
case
when [PRODUCT_NUMBER] = '4110' then ([PRODUCTION_COST] + [GROSS_MARGIN])/2
else
([PRODUCTION_COST] + [GROSS_MARGIN])
end
i am not sure if this is what u were looking for, hope it helps.
Is there a single measure in the cells only, or are your measures the (a,b,c,d) items, or the (Total cost, cost less fuel, avg cost, avg num of days) items?