I have an Product Dimension as follows:
'toothbrush', 'paste', 'floss'

I have a Region Dimension as follows:
'Reg 1', 'Reg 2', 'Reg 3', ... , 'Reg N'

I have a measure called 'itemssold' that stores integer value of items sold.

I want to create a calculated measure that will return the nth value(NON
EMPTY AND > 0) of the set for a particular Product.

Example:
I want to create calculated member called "toothbrush_80th_percentile"
uing following data:
REG1 REG2 REG3
REG4 REG5
Dimension [Product].[toothbrush] 10 20 0 empty
18

I then need to filter/sort this as follows:
REG1 REG5 REG2
Dimension [Product].[toothbrush] 10 18 20

I would take the 80th percentile by using # of records in the set (3) * .8
= 2.4.
Truncate to 2.
Find position 2 in the filtered/sorted set and Return 18.

How can I accomplish this without calling external functions?

Thank You,

Daniel J. Zaccarine
Hanford Bay Associates, Ltd.

Hi Dan,

Assuming that you need a calculated member on the Region dimension,
which works with various measures:

'Tail(BottomCount(Filter([Region].[Region].Members,
Not IsEmpty(Measures.CurrentMember)
And Measures.CurrentMember > 0) as FilteredRegions,
Int(0.8 * FilteredRegions.Count),
Measures.CurrentMember)).Item(0)'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks. I was able to get this to work in an RS report, however it is too
slow. I calculate multiple percentiles per page.

Is it possible to store this as a measure directly in the cube so I can
speed up the query? I tried creating a calculated member but it did not
work. I tried modifying solve order so when the cube is processed it
created the averages I need before calculating the percentile, but I was
unable to get it to work.

Example:
Calculated member "Reg 80Pctile":
'Tail(BottomCount(Filter([Region].[Region].Members,
Not IsEmpty(Measures.CurrentMember)
And Measures.CurrentMember > 0) as FilteredRegions,
Int(0.8 * FilteredRegions.Count),
Measures.CurrentMember)).Item(0)'


Thank You,
___________________________

Daniel J. Zaccarine
Hanford Bay Associates, Ltd.

___________________________
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

TopCount() might be faster for 80 Percentile:

'Tail(TopCount(Filter([Region].[Region].Members,
Not IsEmpty(Measures.CurrentMember)
And Measures.CurrentMember > 0) as FilteredRegions,
1 + FilteredRegions.Count
- Int(0.8 * FilteredRegions.Count),
Measures.CurrentMember)).Item(0)'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***