I have the following pivot table with columns for "Sales Region" and "Revenue"
Sales Region Revenue Northeast $ 4,000,000.00 Midwest $ 2,000,000.00 South $ 1,750,000.00 West $ 3,000,000.00 Total $ 10,750,000.00
I want to add a third column that displays the Top Sales Rep within that region, like so:
Sales Region Revenue Top Sales rep Northeast $ 4,000,000.00 Mike S Midwest $ 2,000,000.00 Wanda R South $ 1,750,000.00 Tom B West $ 3,000,000.00 Sarah T Total $ 10,750,000.00
The data comes from the following table:
Fact_Sales Sales Region Sales Rep Revenue Northeast Mike S $ 3,000,000.00 Northeast Bob J $ 1,000,000.00 Midwest Wanda R $ 2,000,000.00 South Julie T $ 500,000.00 South Tom B $ 750,000.00 South Chad R $ 500,000.00 West Harry E $ 1,000,000.00 West Marcus G $ 500,000.00 West Sarah T $ 1,500,000.00
I was able to find the maximum individual revenue for a sales rep within that region by creating the following measure:
But I'm stumped at how to get the pivot to show the actual name of the top sales rep and not just the revenue they earned. I've looked around the forum and other sites, but I wasn't able to find any solutions - it could be because I'm not using the right terminology. Any suggestions?