Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS Median Function Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 08, 2011 12:13 PM
Points: 5, Visits: 20
This is going to sound pretty bad but I have never used a function in SSAS to date. I have always been able to work around it and now I have a need to figure the Median of a value but don't know where to even start. Online help was nothing but confusing telling me to create an mdx query, I have to believe it's much easier then that.

Can someone out there do a bit of hand holding while I get this function to work against an existing measure within the cube.
Post #912926
Posted Sunday, May 02, 2010 4:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 03, 2010 5:15 AM
Points: 57, Visits: 83
Hello Ken.

If your intention is to query a cube then Mdx is your query-language. MDX has a built-in Median function you can use. The following (MDX) query results in the medium value for the internet sales count-measure based on the cutomer education.

with member [measures].[median internet sales count] as 
MEDIAN( [Dim Customer].[Education].[Education].members ,[Measures].[Internet Sales Count] )

select {[measures].[median internet sales count]} on 0
from [Adventure Works DW]

The outcome of this query is:

median internet sales count
10603

By querying the measure Internet Sales Count by Customer Education we can validate that the median has been caluculated correctly.

select {[Measures].[Internet Sales Count]} on 0,
order([Dim Customer].[Education].[Education].members, [Measures].[Internet Sales Count], asc) on 1
from [Adventure Works DW]

which results in:

	Internet Sales Count
Partial High School 4,708
High School 10,320
Graduate Degree 10,603
Partial College 16,623
Bachelors 18,144

QED

Hope this helps you out.

Cees


Post #914420
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse