Percentile in pivot table

[a_kosarev]

Hi everyone! 

I'm trying to create a pivot table, with one of the columns showing average of values that a less than the 75th percentile. The rows of the pivot table a different types of work. 

If I try to calculate the 75th percentile separately, everything works fine. 

=Fractile(Column1, 0.75)

But if I try to put this formula inside the average formula: 

=avg({<Column1 = {"<$(=Fractile(Column1, 0.75))"}>}Column1)

it calculates percentile of all values, ignoring the division into rows, and therefore the average is not correct. 

Can I somehow make it calculate correct percentile for every kind of work? 

upd: 

If I choose one of the rows by clicking on it and thus make a filter apply, p75 avg values change for correct. But if I dissolve the filter and make all rows appear again, it goes back to wrong values.

[Anil_Babu_Samineni]

How about this?

=avg({<Column1 = {"='<'&Fractile(Column1, 0.75))"}>}Column1)

[sunny_talwar]

Set Analysis is global in nature and is evaluated once per chart instead of once per dimension... try this instead

=Avg(Aggr(
    If(Column1 < Fractile(TOTAL <Division> Column1, 0.75), Column1)
, Column1, Division))

[a_kosarev]

Hi, Talwar

thank you very much, now it works fine with the dimensions. 

But now the total row in the pivot table (the bold one at the top) calculates incorrect value. 

Is there any way to get both total and dimensional values correct?  

[sunny_talwar]

May be this?

=If(Dimensionality() = 0,
 Avg({<Column1 = {"<$(=Fractile(Column1, 0.75))"}>}Column1),
 Avg(Aggr(
    If(Column1 < Fractile(TOTAL <Division> Column1, 0.75), Column1)
 , Column1, Division))
)

[a_kosarev]

=If(Dimensionality() = 0,
 Avg({<Column1 = {"<$(=Fractile(Column1, 0.75))"}>}Column1),
 Avg(Aggr(
    If(Column1 < Fractile(TOTAL <Division> Column1, 0.75), Column1)
 , Column1, Division))
)

This one works just as well with dimensions, but shows nothing in totals (the "-" symbol). 

[sunny_talwar]

Really, would you be able to share a sample where we can see the issue?

[a_kosarev]

Here it is

[sunny_talwar · Accepted solution]

Here you go

If(Dimensionality() = 0, 
Avg({<Completion_Time = {"<$(=Fractile(Completion_Time, 0.75))"}>}Completion_Time), 
Avg(Aggr( If(Completion_Time < Fractile(TOTAL <Work_type> Completion_Time, 0.75), Completion_Time) , Completion_Time, Work_type)))

[a_kosarev]

It work perfectly, thank you for your help!