# Thread: Percentile with MDX ???

1. Registered User
Join Date
Dec 2007
Posts
2

## Percentile with MDX ???

How to create Percentile with MDX??

i got this function but i'm not success to follow up.

Percentile ( set, numeric_value_expr, percentile )

(set -> The set from which to get a tuple value.
numeric_value_expr -> A numeric value or an expression that returns a numeric value.
percentile -> A percentile. Must be between 0 and 100. )

Thank you

2. Registered User
Join Date
Nov 2009
Posts
2
Hello Aftera,

Wonder if you found the solution to this? I got some answer, but it does not look quite like what I have expected it to be
naaziz

3. DBJ Author
Join Date
Jun 2004
Location
Atlanta and Manhattan
Posts
607

## Are We Talking Essbase here?

Just making sure ...

Bill

4. Registered User
Join Date
Nov 2009
Posts
2
Hi,
We are talking about MDX query used to calculate percentile...
Atleast this is what I understand from the post above.

thanks

5. DBJ Author
Join Date
Jun 2004
Location
Atlanta and Manhattan
Posts
607

## The Percentile Function in Essbase?

The only Percentile function with which I have any exposure is the Essbase MDX function. Is this the function to which you are referring?

Example:

WITH MEMBER [Measures].[Perc] AS
'Percentile(Products.Levels(0).Members, [Measures].[Price Paid], 10)'
SELECT {[Measures].[Price Paid], [Measures].[Perc] } ON COLUMNS,
{ Products.Levels(0).Members } ON ROWS
FROM AsoSamp.Sample

Per the documentation, "this function orders the set according to the numeric value expression. It then finds the tuple at the given percentile and returns its value ... The returned value is such that n percent of the set members are smaller than it."

Is this what you are trying to accomplish? Where are you trying to use the function?

Thanks.

Bill

6. Registered User
Join Date
Dec 2007
Posts
2

## Percentile with MDX ???

Hello naaziz,

for my Percentile , i didn't use mdx function.
i created 5 variables for calculate Percentile follow stat step.

a. find position of P25
b. rank member min to max
c. calculate proportion

(i think it not the best way because it's not flexible and create with long syntax. i think mdx function it's the best way but i cannot create with it)

example -Percentile 25

1.[Sales Rank]

iif(IsEmpty([Measures].[Sales_value]), NULL,
Rank([Dept Member].[Dept Member].CurrentMember,
Filter(Order([Dept Member].[Dept Member].CurrentMember.Level.Members,
([Measures].[Sales_value]), BASC),
isempty([measures].[Sales_value])=0)))

2.[Dim_N]
count(Filter( Order([Dept Member].[Dept Member].[Dept Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC) ,
([Measures].[Sales Rank] <> null ))

3.[PR-25]
25*([Dept Member].[Dept Member].[Dim_N]+1)/100

4.[Dim_Percentile 25]
MAX(Filter( Order([Dept Member].[Dept Member].[Member Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC)
,([Measures].[Sales Rank] = Int([Dept Member].[Dept Member].[PR-25]))))+((MAX(Filter( Order([Dept Member].[Dept Member].[Member Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC) ,
([Measures].[Sales Rank] = Int([Dept Member].[Dept Member].[PR-25]+1))))- MAX(Filter( Order([Dept Member].[Dept Member].[Member Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC) ,([Measures].[Sales Rank] = Int([Dept Member].[Dept Member].[PR-25]))))
)*([Dept Member].[Dept Member].[PR-25]-int([Dept Member].[Dept Member].[PR-25])))

5.[Percentile 25]
([measures].[Sales_value],[Dept Member].[Dept Member].[Dim_Percentile 25])

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•