4 Replies Latest reply on Jun 2, 2016 12:09 PM by John Didlott

# Count the number of records that make up the Percentile

I have been able to create my percentile fields, but have not been able to create how many records make up that percentile.  I have tried numerous formulas and have not gotten anywhere other than learning how to create some other formulas.  Any ideas?  I have attached the packaged workbook.

Thanks,

• ###### 1. Re: Count the number of records that make up the Percentile

John,

Are these the numbers you are looking for ?

As you can see, I made a copy of Expense Amount and convert it to a dimension.

This way I can use it in a lod calculation to compare each Amount with the percentile value.

So if you look at PCT75 -nbRec

SUM( {FIXED [Exp_Type],[Expense Amount (copy)]: SUM( if  [Expense Amount] <= [PCT75] then [Number of Records] end )} )

,  I check for every [Exp_Type],[Expense Amount (copy)]  group,  if the Expense Amount is lower than the Percentile value, then I SUM the number of records.

Also since the percentile computation is nested in a lod calc that includes [Expense Amount (copy)], I have to exclude this dimension since the Percentile needs to be computed only on the Exp_Type  level of detail.

{EXCLUDE [Expense Amount (copy)]:PERCENTILE( [Expense Amount],0.75 )}

Michel

• ###### 2. Re: Count the number of records that make up the Percentile

Hi,

I looked at the workbook attached and did not see your calculations in it, but when I duplicated the amount field and changed it to a dimension I received an error around the [PCT75] field reference (reference to undefined field [PCT75]).

Also, where are you using the exclude formula?  Is this another calculated field?

John

• ###### 3. Re: Count the number of records that make up the Percentile

oops!  I attached the original and not the updated version.  Here you go...

• ###### 4. Re: Count the number of records that make up the Percentile

Thank you,

This worked perfectly!