42550

MDX average function counting on

Question:

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

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

This is my query:

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?

Answer1:

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];

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.

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]

Recommend

  • IcCube - accessing Report Code with Javascript
  • facebook audience network testing on test flight No Fill
  • SSRS 2012. Replicate grouping results in report in Query Designer
  • Accessing a html page that is in a tab of a html page using Windows Phone 8 app
  • Cannot find a differ supporting object '[object Object]'
  • Reason to integrate Spring Web-flow with Spring MVC
  • Laravel - time difference
  • kops - get wrong kubectl context
  • Display count value in the mysql database table using handlebars in Nodejs project
  • Find data for each subsequent financial period year in mdx query
  • Question on join where one column one side is null
  • Pandas ordered categorical data on exam grades 'D',…,'A+'
  • MDX Get MAX MEMBER_KEY from Dimension
  • Android Studio NDK: Compile with ndk-build and get native support with Gradle Experimental
  • Storm latency caused by ack
  • jquery pie chart with drilldown/clickable pie parts
  • complex annotate on django query set
  • How to browse android SDK sources in Android studio 2.0
  • Why does the first run of “XCTestCase -measureBlock:” takes so much time?
  • Taking mean across rows grouped by a variable in numpy
  • Is it possible to ask GIT, don't show a file?
  • How to generate an asynchronous reset verilog always blocks with chisel
  • Neo4j: Filter nodes based on aggregate function
  • How to create two column output from a single column
  • jQuery ready not fired after rails link_to is clicked
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Yii2: Config params vs. const/define
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Algorithm for a smudge tool?
  • Email format validation in mvc3 view
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • How to add date and time under each post in guestbook in google app engine
  • Sending data from AppleScript to FileMaker records
  • Display Images one by one with next and previous functionality
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Load html files in TinyMce
  • coudnt use logback because of log4j