I have sales table for different branches…
What I need is a pivot table with Dimensions
Expression : SUM(Sales)
I need a pivot table showing top 5 sales operator of each branch and their monthly sales achievements.
If I select Branch 01... result would be something like below...
|Branch Code||Sales Operator||Month||Total||May||April||August||July||June||September|
looking at your file I think I have achieved what you need. This is with the following sort:
Branch Code (no sorting selected)
Sales Operator (expression - sum(Sales) Descending
Month (no sorting)
This sorts according to the top sales person.
In a pivot table itself, there is no option to reduce to just show top 5. However, you could consider another chart (bar chart) where you can restrict presentation to only show 5. (Enable x axis scrollbar)
I have attached my example. I hope this helps. Please mark as answered if this helps.
hope this expression will work ..
Add calculated dimension, instead of [Sales Operator]
=If(Aggr(Rank(Sum(Sales)), [Sales Operator])<=5, [Sales Operator], 'Others')
you will have 6 rows, 5 top and 1 for others.
If you dont want to show others in the pivot table, then use 'Null' like below
=If(Aggr(Rank(Sum(Sales)), [Sales Operator])<=5, [Sales Operator], 'Null')
and check suppress null value on presentation tab, you will see exactly 5 rows.
Hope it helps...
Thanks for your reply. I have done it my self by using the same by below expression... your expression is not working...
=IF(AGGR(RANK(SUM(Sales)),[Branch Code],[Sales Operator])<=5, [Sales Operator])
One more question.... how can I color the dimension cell ?
If May sale for particular Operator is higher in respective branch than previous month i.e. April... the background color should be Green or Red
Sure any expression will not work exactly, without seeing data model.
but meant to give an idea, and off course you have to make little changes in that, but that helped you..
for background color, put your condition here in background color, see the image