Hello,
I have a crosstab with calulated fields as rows (reasons).
The column is weekly count data based on a dimensaional data source devived for a prompt. I would like to have a weighted average column as well. But I'm having no luck:
The average wizard doesn't return any values.. My attempt to write a calulated field to work out the avg isn't working either???
example:
reasons Week End Weight Avg
'ABC' 20 10%
'BCD' 60 30%
'EFE' 120 60%
----------------------------------
totals 200
- Can anyone help?
Cheers ??? ???
Weight Avg = total (week end for reasons) / total (week end for report)
and format as %
Suraj,
Thanks for your response.. The issue that I'm having is that my reasons 'ABC' etc.. are sets of reasons, from the reason dimension.
I believe that is the issue - I'm still not able to get the weighted average from the sets of reasons in my crosstab. Is there a way of calulating the average of the sets of reasons?>>
Thanks,
try the same calculation in a list report.
if you see data there but not in crosstab, play with Aggregate/Rollup Aggregate properties; change to calculated...etc..
Suraj,
Thanks for your help - I finally got it!
I created a Member set for my Reasons, for the underling query from the dimensional data source.
Then used:
[measure] / aggregate([measure] within set [Reasons])
Cheers!!