 Calculate Average Across then Up instead of Up ten Across then up • 질문

• I have a stack of surveys with questions grouped by sections. The total score for each section and survey is the average of the question scores. I need to find the average score of each section and each survey at the individual survey level, and then average all of those totals for all of the surveys. The grain is one row per question. I can't pre-calculate the section and total scores because those will be different depending on how the questions are filtered.

What I have works but is a little slower than I'd like. [Avg Survey Scale Score] is a dummy column that aggregates as a Sum. The big issure right now is the All level is too slow. Is there a better way to do this?

SCOPE ([Measures].[Avg Survey Scale Score]); /*-------------------------------------------------------------------------------------------------------- At the Survey Encounter level, each level of the [Question] hierarchy is calculated as the average of the scale scores. Above the Survey level, each level is calculated as the average of the Survey scores --------------------------------------------------------------------------------------------------------*/ // Question level Score SCOPE [Question].[Question ID].[All].Children; this = IIF([Survey Encounter].[Survey Encounter ID].CurrentMember.LEVEL.ORDINAL = 1, [Measures].[Scale Score], IIF ([Measures].[Survey Response Qty] = 0, NULL, [Measures].[Scale Score] / [Measures].[Survey Response Qty] ) ); format_string(This)="#,##0.00"; freeze; END SCOPE; // Section Level Score SCOPE [Question].[Section].[All].Children; this = IIF ([Measures].[Survey Response Qty] = 0, NULL, IIF([Survey Encounter].[Survey Encounter ID].CurrentMember.LEVEL.ORDINAL = 1, [Measures].[Scale Score]/[Measures].[Survey Response Qty], SUM( NONEMPTY( [Question].[Question Section].CurrentMember * [Surveys],[Measures].[Survey Response Qty]) ,[Measures].[Avg Survey Scale Score]) / [Measures].[Section Survey Qty] ) ); format_string(This)="#,##0.00"; freeze; END SCOPE; // Overall and leaf level Score this = IIF ([Measures].[Survey Response Qty] = 0, NULL, IIF([Survey Encounter].[Survey Encounter ID].CurrentMember.LEVEL.ORDINAL = 1, [Measures].[Scale Score] / [Measures].[Survey Response Qty], SUM( NONEMPTY([Surveys],[Measures].[Survey Response Qty]) ,[Measures].[Avg Survey Scale Score] ) / [Measures].[Survey Qty] ) ); format_string(This)="#,##0.00"; END SCOPE;

• 편집됨 2012년 9월 28일 금요일 오후 7:02 Typo
2012년 9월 28일 금요일 오후 7:01

모든 응답

• Hi Preston Park,

Thank you for your question.

I am currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.

Thanks,
Eileen

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

2012년 10월 1일 월요일 오전 9:19
• Thanks. The example script doesn't do what I thought it did.
2012년 10월 1일 월요일 오후 2:31