Need to show top 20 locations by sales

[reema_dangwal]

Hi All,

I need to show top 20 locations by sales, however not able to achieve the same in straight table/pivot table using dimension limits or aggregate function.

PFA the application file.

If a location is lying in top-20 group, it is displayed for other institutes as well.

The required output should look like following but not getting this.

Institution	Location	Total Sales
TCSS	Tower Hall	12,23,390.
MSU	Alumni Tower	6,81,855.
CC	Berry Hall	6,55,460.
MSU	Third Street Eats	5,89,740.
UCF	Move-In Weekend	5,66,850.
TUS	FFCO 3rd Floor	3,83,936.
TU	SC	3,36,957.
UCF	SU	2,97,715.
CU	Academic Circle	2,77,680.
WCUP	Lawrence	2,23,565.
UV	FFC	1,95,006.
WCU	Courtyard FC	1,90,008.
BUP	1st Floor Commons	1,79,479.
USM	Thad Cochran	1,76,030.
WCU	UC FC	1,50,041.
GVSU	Connection	1,49,125.
MSU	Academic Insight	1,48,180.
GVSU	FFC	1,23,450.
SLU	SU	50,805.

Thanks.

Reema

[dapostolopoylos]

You could use a straight table and dimension limits tab for the top 20 Sales

[sunny_talwar · Accepted solution]

Try with this expression

If(Rank(TOTAL Sum([Total Sales])) <= 20, Sum([Total Sales]))

[raviityou]

Hope your looking for the same, find attached file.

[reema_dangwal]

Thanks, this is working however pivot table is not getting sorted as straight table is sorted in descending order.

Even after giving expression for sorting.

Is it feasible to sort the pivot table too?

[sunny_talwar]

You can look here for help with sorting in pivot table

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

[pintucs20]

Hi Reema,

You could use a straight table and dimension limits tab for the top 20 Sales.