5 Replies Latest reply on Feb 7, 2017 4:20 AM by Simon Runc

# Want to show all the Categories and next to it  and Name of Customer with Highest sales

Want to show all the Categories and next to it  and Name of Customer with Highest sales. Category and Customer both are dimensions.

• ###### 1. Re: Want to show all the Categories and next to it  and Name of Customer with Highest sales

hi Shripal,

As always with Tableau there are many ways to solve this...I've gone for an LoD approach, as it means you don't need the Customer in the Level of Detail (but we could use RANK and filter on 1 instead, but would need customer in the Viz)

So first I want to return the MAX Sales by a Customer for Each Category

[Max Customer - Sale per Cat]

{FIXED [Category]: MAX({FIXED [Category],[Customer Name]: SUM([Sales])})}

and then I want to return the customer name, where there sales by category is equal to this value

IF {FIXED [Category],[Customer Name]: SUM([Sales])} = [Max Customer - Sale per Cat] THEN [Customer Name] END

So this field now only contains the Top Customer per Category, and everyone else is NULL...I can then bring this in and filter to exclude NULLs. There is also a "Check" sheet so you can see what it's doing

Hope that helps, and makes sense...let me know if not

1 of 1 people found this helpful
• ###### 2. Re: Want to show all the Categories and next to it  and Name of Customer with Highest sales

Hi Simon,

Thank you very much. It is working perfectly well.

On top of it, i have one more query. I dont want to show, if Customer is ''XYZ". In that case, i want to show the second highest sales customer.

Thanks,

Shripal

• ###### 3. Re: Want to show all the Categories and next to it  and Name of Customer with Highest sales

...so if we want the 2nd, 3rd...etc. we have to use a different method. With an LoD we can pick up the top (MAX) and bottom (MIN) but need to use RANK (a table calculation) in order to get other positions.

On the attached ("Top N Customers - RANK" Tab) I've created the following calc

[Customer RANK]

RANK(SUM([Sales]))

I bring this into the view, and set the compute using to "CustomerName" (as we have Category in the View it will create a RANK for each Customer Name, restating the RANK every Category.

Once we have this we can just use this field as a filter (in the attached I've made it continuous so we get a slider...but it could be a drop-down, or just set to 2 with out exposing it to the user)

1 of 1 people found this helpful
• ###### 4. Re: Want to show all the Categories and next to it  and Name of Customer with Highest sales

Thanks Simon,

I see, you have used Sales Sorter Calc field. What if the values range from negative to positive.?

• ###### 5. Re: Want to show all the Categories and next to it  and Name of Customer with Highest sales

This is just a trick to "force" a nested sort (I really only put it in there so it was easier for you to see what's happening, everything would work the same without it)

...Tableau orders everything to the right of a blue pill in "Ascending" order, by default. As such by creating a negative version of the measure we want to sort on, we get a "descending" sort. As customers buy into multiple categories, and the way Tableau's (in built) sort works, it would sort the customer on their total sales (and not sorted by Customer/Category sales).

If there were negative numbers they would become positive (the *-1) and so would go below any negative numbers (negative in the sort field, they would be positive numbers to begin with...if that makes sense?!)