none
Display Top Value within Row

    Question

  • 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:

    =max(fact_sales[revenue])

    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?

    Tuesday, April 24, 2012 12:49 AM

Answers

All replies