1 Reply Latest reply on Jan 2, 2019 7:55 PM by Hari Ankem

    Creating Relative Rank by Category for a Given Customer

    Dan Maycock

      I have a workbook that has a series of packaging types (Clamshell, Corrugate, Misc, etc). Each customer has total sales by some if not all of those categories, based on what was sold to them in the previous year.

       

      What I'm trying to do is build a rank that shows how that customer did in the given category compared to all other customers. All I'm able to do right now is how each of the categories rank against each other, when I select a given customer vs ranking those sales by category across all customers.

       

      Current Example (categories ranked against each other for the given customer)

       

      Customer A

       

      Category A: 1

      Category B: 2
      Category C: 3Category D: 4

       

      How it should read, if compared against all customers (each category is ranked for that given category vs all other customers not selected)

       

      Customer A:

       

      Category A: 124

      Category B: 15

      Category C: 35

      Category D: 45

       

      Does anyone know how to build this kind of rank?

       

      Thanks!

        • 1. Re: Creating Relative Rank by Category for a Given Customer
          Hari Ankem

          See if this will help you get to your solution. In the Superstore data, I am using the Rank table calculation to display the rank of the cities with each state, as well as across all the states based on the sales for each city.

          1.png

           

          For the first rank field you see above, it has been computed as shown below, and it gives the ranking of the city within each state.

          1.png

           

          For the second rank field you see above, it has been computed as shown below, and it gives the ranking of all the cities across all states.

          1.png

           

          Hope this helps.