How to caluculate Quintile/NTile?

Question

Hello,

Can anyone help me calculate a Quintile from my data. The query below Ranks my records & also calculates Percentiles. However, I am stuck with figuring out how to calculate Qunitile or for that matter of fact any NTile. Thanks in advance!!!

WITH

 

 
SET [CustomersRankSet] AS
FILTER(
       ORDER(
            [Dim Area].[Area Name].CHILDREN
           ,[Measures].[Customers]
           ,BDESC
       ), [Measures].[Customers] > 0
)

 

 

MEMBER [Measures].[CustomersRank] AS
RANK(
        [Dim Area].[Area Name].CurrentMember
       ,[CustomersRankSet]
)

 

 

MEMBER [Measures].[Count] AS
COUNT(
       NONEMPTY(
              [Dim Area].[Area Name].MEMBERS
             ,[Measures].[Customers]
       )
)

 

 

MEMBER [Measures].[Percentile] AS
1 - [Measures].[CustomersRank] / [Measures].[Count]
,FORMAT_STRING = 'Percent'

 

 

MEMBER [Measures].[Quintile] AS
0

SELECT

 

{
                   [Measures].[Customers]
                  ,[Measures].[CustomersRank]
                  ,[Measures].[Percentile]
                  ,[Measures].[Quintile]
            } ON 0
           ,[CustomersRankSet] ON 1
FROM   [My Cube]

Thanks,
Irfan

Answer 1

Is this is a good way to calculate Quintile? Note: Eveything else is same in the MDX query above, I am only adding logic for Quintile here

 

MEMBER [Measures].[Quintile] AS
ROUND(([Measures].[Percentile]*5) - .5)

If there is any better way to calculate quintile please let me know. I am not sure how much performance intese this is.

Thanks,
Irfan