In my Fact Table, I've the following fields:
MenuKey
DateKey
TimeKey
MenuDuration (whole numbers in seconds)

The Dimensions I've:
Menu
Date
Time

For the following selections:
Date = 10/2/2006
MenuID = 1010
Time = 4:30-4:35 PM (5 min interval)

I've one menu finished in 0 second, 2 in 1 second, 5 in 2 seconds, and 2 in
3 seconds. What I need is to find out in how many seconds, 80% are finished.
In this case, I should get 2 seconds. I cannot figure out how to write the
MDX for this though. TIA.

Duration Seconds Count Accumulated Percentage
0 Sec 1 10%
1 Sec 2 30%
2 Sec 5 80%
3 Sec 2 100%

If you have a dimension where each leaf member corresponds to 1 row of
menu time data, that will permit percentile analysis. For example, if
each leaf member of [Menu] has a single row, the 80 percentile value
would be like:

([Measures].[MenuDuration],
Order([Menu].[MenuID].Members,
[Measures].[MenuDuration], BASC)
.Item(Int(((4 * Count([Menu].[MenuID].Members)) - 1)
/ 5)).Item(0))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

I created a calculated member using the code you posted. For some reason,
it returns the same number for all menus. Am I missing anything? Thanks!

"Deepak Puri" wrote:

Deepak,

I'm using the 5-min intervals as the X-axis, [Measures].[MenuDuration]
becomes [Measures].[SumMenuDuration] for each 5 minutes. I'm not sure how to
make it work since there can be multiple fact records with the exact same
date, time and menu data. Thanks!!


"Deepak Puri" wrote: