Rank with an Avg column (Or only show first 5 records)

[#1]

Hi,

I am attempting to only show the top 5 / worse 5 using an average.

I have sorted the Pivot Table, with the expression:

((Avg(Price)-DefaultPrice)/DefaultPrice)

This is also the definition of a column in the table.

But when I attempt to use the Rank function:

RANK(((Avg(Price)-DefaultPrice)/DefaultPrice))

all values are 1.

How can I restrict the table to only show the first 5 records, when using the order expression above?

I am using v10

[#2]

If I have the data:

PartNo	ProductName	Reseller	DefaultPrice	Price
Product 1	Product 1	Reseller1	£65.00	£76.40
Product 1	Product 1	Reseller2	£65.00	£84.77
Product 1	Product 1	Reseller3	£65.00	£124.76
Product 1	Product 1	Reseller9	£65.00	£57.91
Product 2	Product 2	Reseller8	£82.50	£150.54
Product 2	Product 2	Reseller2	£82.50	£107.14
Product 2	Product 2	Reseller3	£82.50	£113.08
Product 3	Product 3	Reseller1	£165.00	£177.11
Product 3	Product 3	Reseller8	£165.00	£179.99
Product 3	Product 3	Reseller2	£165.00	£214.41
Product 3	Product 3	Reseller3	£165.00	£304.68

I only want to see the products where the expression:

Rank(TOTAL((Avg(Price)-DefaultPrice)/DefaultPrice))

Is less than 6

So at the moment in the Pivot Table, I have the following Dimensions

• Part No,
• Product Name
• Default Price

And the following as expressions:

• ((Avg(Price)-DefaultPrice)/DefaultPrice)
• Avg(Price)
• PriceIncVat-Avg(Price)

Hope that makes sense.

[#3 · Accepted solution]

Ended up ordering in SQL and returning only the top 5 results.

A bit pants that there appears to be no other way of doing it.

[Jason_Michaelides]

There will certainly be another way to do it.  If you could post your app it would help us provide a solution for you.

[richnorris]

The way I've done it is just to work out what the average is, order by rank and then on the presentation tab, check 'Max Number' and set it to ten. (You could set it to five.) Likewise for 5 worst, you would just do a sort on the rank ascending instead of descending.