1 Reply Latest reply on Jun 12, 2015 9:55 AM by Joshua Milligan

    Summary of Rank by Market Calculation

    Drew Hull

      I am trying to create a summary table that shows the percentage of markets where each sales rep is ranked #1 in sales.

      In the attached workbook:

      Sales Rank Table - shows each Sales Rep's rank by market

      Rank Summary Table - this is where I need help....I need a calculation that will show Angie - 20%, Beth - 40%, Charlie - 10% and David 30%


      Thanks for your help!

        • 1. Re: Summary of Rank by Market Calculation
          Joshua Milligan



          You can do this by layering / nesting some table calculations, similar to this:


          • Make your quick table calculation a calculated field by dragging it from the view into the Measures area of the Data Window.  That's because I'll reference it in additional calculations.  I've named the calculation [Sales Rank]
          • Make a calculation that adds 1 for each #1 Rank:


          • We can use the Size() function to determine the number of markets


          • Make one more table calculation that calculates the percent.


          • When you add these new calculations to the view, you'll have to make sure to edit the table calculation (right click the field and select Edit Table Calculation).  Use the drop down to set which layer of table calculation you want to change.  Make sure the Sales Rank is always set to Compute Using: [Sales Rep] and that the new calculation is set to Compute Using: Market.


          • Now, you can add the calcs into the view and you'll get the right results.  The only issue is that the right results are duplicated for each Market.


          • Then, I can filter using the First() function to keep only the first market in the view.  Market has to stay in the view to define the level of detail, but I can move it to Detail so it doesn't show.  And the final view looks like this:




          Hope that helps!


          Best regards,