MDX平均功能依靠 [英] MDX average function counting on

问题描述

我偶然发现了一个我无法解决的问题。在我的学术项目中，我有一个代表航空公司数据仓库的多维数据集。在这家航空公司中，乘客用5个问题填写问卷，对从1到10的航班各个方面进行评分。

I stumbled upon a problem which I cannot resolve. For my academical project I have a cube representing an airline's data warehouse. In this airline, passengers fill out a qustionnaire with 5 questions, rating various aspects of the flight from 1 to 10

我被要求写一个 MDX 查询，以显示他们的航班总体等级（五个等级的平均值）取决于他们所在的班级。

I was asked to write an MDX query to show how their overall grade of the flight (average of five grades) depending on the class they were in.

这是我的查询：

 WITH 
  MEMBER [Average Grade1] AS 
    [Measures].[Grade1] / [Measures].[Flight Count] 
  MEMBER [Average Grade2] AS 
    [Measures].[Grade2] / [Measures].[Flight Count] 
  MEMBER [Average Grade3] AS 
    [Measures].[Grade3] / [Measures].[Flight Count] 
  MEMBER [Average Grade4] AS 
    [Measures].[Grade4] / [Measures].[Flight Count] 
  MEMBER [Average Grade5] AS 
    [Measures].[Grade5] / [Measures].[Flight Count] 
  MEMBER [Average] AS 
      (
            [Measures].[Average Grade1] + [Measures].[Average Grade2]
          + 
            [Measures].[Average Grade3]
        + 
          [Measures].[Average Grade4]
      + 
        [Measures].[Average Grade5]
      )
    / 5 
SELECT 
  {
    [Measures].[Average Grade1]
   ,[Measures].[Average Grade2]
   ,[Measures].[Average Grade3]
   ,[Measures].[Average Grade4]
   ,[Measures].[Average Grade5]
   ,[Measures].[Average]
  } ON COLUMNS
 ,{[Junk].[Class].Children} ON ROWS
FROM [Airline];

由于给出了错误的结果，因为平均值是在按类别实际分组之前计算的，因此得出相同的结果针对所有课程显示。
谁能告诉我如何获得想要的结果？

It gives wrong results because the average is counted before the actual grouping by class that's why the same result is shown for all classes. Can anyone tell me how to get the result I want?

推荐答案

正如mmarie所说，这通常是获取平均值的方法：

As mmarie mentions this would usually be the way to get an average:

WITH 
  MEMBER [Average Grade1] AS 
    [Measures].[Grade1] / [Measures].[Flight Count] 
  MEMBER [Average Grade2] AS 
    [Measures].[Grade2] / [Measures].[Flight Count] 
  MEMBER [Average Grade3] AS 
    [Measures].[Grade3] / [Measures].[Flight Count] 
  MEMBER [Average Grade4] AS 
    [Measures].[Grade4] / [Measures].[Flight Count] 
  MEMBER [Average Grade5] AS 
    [Measures].[Grade5] / [Measures].[Flight Count] 
  MEMBER [Average] AS 
      (
            [Measures].[Grade1] + [Measures].[Grade2]
          + 
            [Measures].[Grade3]
        + 
          [Measures].[Grade4]
      + 
        [Measures].[Grade5]
      )
    / [Measures].[Flight Count]
SELECT 
  {
    [Measures].[Average Grade1]
   ,[Measures].[Average Grade2]
   ,[Measures].[Average Grade3]
   ,[Measures].[Average Grade4]
   ,[Measures].[Average Grade5]
   ,[Measures].[Average]
  } ON COLUMNS
 ,{[Junk].[Class].Children} ON ROWS
FROM [Airline];

如果度量未与 MDX上下文相关脚本，然后您将得到重复的答案。因此，如果您使用的度量未与上下文关联，即 [Junk]。[Class] .Children ，则将基于默认成员评估得到重复。

If a measure is not connected to the context of an MDX script then you get repetition of the answer. So if the measures you are using are not connected to the context i.e. [Junk].[Class].Children then you will be getting repetition based on default member evaluation.

尝试将 [Junk]。[Class] .Children 换成与您100％相关的其他维度度量 [度量]。[Grade1] 和 [度量]。[飞行计数]

Try swapping out [Junk].[Class].Children for a different dimension that you know is 100% related to the measures [Measures].[Grade1] and [Measures].[Flight Count]

这篇关于MDX平均功能依靠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！