Limiting top 5 values in pivot table by percentage

[infock12]

Hello all,

Good morning. I have a pivot table with two dimensions, Student and Category

The expression I have is =Num(count({<Rating={'>=7'}>} Questions)/Count(Questions), '0.00%')

I am hoping to limit the values to top 5% categories. I am aware I can add a calculated dimension but not getting the formula right...Any help is appreciated!

Student           Category         Top 5%

A                      Science

B                      Maths

C                      English

Thanks.

[infock12 · Accepted solution]

Hi all,

I think I figured it out. I used =if(Aggr(Rank(Num(count({<Rating={'<7'}>} Questions)/Count(Questions)), '0.00%'), Category)<=5, Category). It works.

However, I am unable to supress the subtotal (or total average)....It appears at the bottom of every section and 'No totals' section under Expression tab is greyed out. How will I hide the subtotal please?

Thanks!

[maxgro]

for a pivot in presentation tab there is a "Show Partial Sums" (upper left)

[infock12]

Hi friends,

I figured that out from another thread here.

Pivot table subtotal coloumn removal

Thanks everyone!