I have sales reps linked to more that one region, and I only want to show the sales rep along with their top selling region and figures.
I have created this dimension which is set to Suppress When Value is Null, which gives us a value of 1 per top selling sales rep/region combination. Else a null. This Null along with the Supress Null checkbox removes all the other regions per Sales rep that we don't want.
=IF(aggr(
rank(
aggr(
sum( {<CommPeriod={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} Actual),
[Planner Code],
[Reporting Region ShortName])
),
[Planner Code],
[Reporting Region ShortName]
)=1,
aggr(
rank(
aggr(
sum( {<CommPeriod={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} Actual),
[Planner Code],
[Reporting Region ShortName])
),
[Planner Code],
[Reporting Region ShortName]
),
NULL())
This works great along with the expression formula:
sum({$<[CommPeriod]={$(vMonthRun)},[Planner In Service Indicator]={Yes}>} [Amount Remuneration PC])
The problem is only with sorting the pivot table, when I sort by the same expression, the figures are sorted according to Sales Reps total figures for all regions. Example a rep with a value of 2 and 4 for 2 regions is ordered above a rep with a value of 5. Although the expression value is correct:
CURRENT RESULT: ACTUAL DATA:
SALES REP TOTAL SALES REP TOTAL
JANE 4 TOM 5
TOM 5 JANE 4
JANE 2
Totals are correct, sorting incorrect. Anyone have any suggestions please
