Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Median Query in MDX

  1. #1

    Default Median Query in MDX

    I am new to mdx and am having some trouble figuring out how to do a median query.

    I have a fact table that looks something like
    date (Dimension)
    player (Dimension)
    round (Dimension)
    balance
    award

    The round dimension right now just contains a number for what round it is.

    I basically want to get the median for the balance and award for each row across all players but am having trouble here is what I have been trying with some variations of.
    WITH MEMBER [Measures].[Median Balance]
    AS MEDIAN({[Round].[Round].CurrentMember.Children}, [Measures].[Balance])
    SELECT {[Measures].[Median Balance]} ON COLUMNS,
    {[Round].[Round].Members} ON ROWS
    FROM [Game Rounds]

    I have tried changing the [Round].[Round].CurrentMember.Children to different things eg. [Round].[Round].CurrentMember, [Round].[Round].Members ex.

    I am still having some trouble grasping how exactly this all works and any help would be great!

    Tyler

  2. #2

    Default

    Hey all I figured this out. I had to give it another value to break the Measures field into a set (vs a sum of all sets) that was outside the round dimension. In my case it is the user.id dimension which breaks it down to a single value for each player's round.

    WITH
    MEMBER [Measures].[Median Balance] AS
    Median([Users].[Id].CurrentMember.Children, [Measures].[Balance])
    SELECT
    {[Measures].[Median Balance]} ON COLUMNS,
    {[Round].[Round].Members}ON ROWS
    FROM [Game Rounds]

    Hope this helps someone in the future!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.