1 2 Previous Next 18 Replies Latest reply on Aug 13, 2019 2:40 PM by Subodh Gupta

# Ranking Top Customer Sales Over Three Years

Hi,

So in the image attached I am essentially trying to sort by the largest total combined sales (which should be the bar chart in orange) for years 2017-2019 and then have it essentially breakdown from in the order of years as it should be reflects (i.e. 2017, 18, 19, etc.). I have a feeling this is an LOD calculation that I need to create or my table calculation is off somewhere. I'm basically utilizing the following dimensions; customer name, invoice year and then my measures are sales. I've created a set that has the top 10 customers sales dollars (picture attached) and then am trying to get it to break down / sort by that aggregate sum of sales dollars.

Just curious if anyone happens to have any insight into how this might be correctly resolved. Thanks much in advance for the help!

• ###### 1. Re: Ranking Top Customer Sales Over Three Years

Based on the limited information, to sort based on the total sales across three years (assuming there aren't any other years in the data) all you need to do is sort the customer (asc/desc) based on field "sales" with aggregation as SUM.

Something like:

then you can sort the year by same

• ###### 2. Re: Ranking Top Customer Sales Over Three Years

When I do this I am only getting a sort by the individual year and not the running total so to speak...sorry if I had not made that clear but essentially I am looking to have it sort by the running total in descending order if that makes sense. I.e. in the screenshot attached the orange customer should be at the top followed by yellow, blue, green, etc. Does that make sense?

• ###### 3. Re: Ranking Top Customer Sales Over Three Years

In the image attached I am able to get to a rank unique for each year's worth of the sum of sales but am looking for it to be sorted by the running grand total..

• ###### 4. Re: Ranking Top Customer Sales Over Three Years

I feel stupid, but rank by running total doesn't make sense to me. Running total is based on how you have sorted the data (rank, so to speak).

Also, do you want it grouped by year or by customer. What I mean is, for each year, sort customers or for each customer, sort the years.

• ###### 5. Re: Ranking Top Customer Sales Over Three Years

If you export that as excel and randomize the customer names, I can see what I can do.

• ###### 6. Re: Ranking Top Customer Sales Over Three Years

I might just be doing a poor job of explaining this and probably should avoid using formulas / calculations in terms of what I am trying to see ...basically in this spreadsheet if I see that customer 1 is more valuable over three years than customer 4, 5, or 6 then I would want customer 1 grouped by year (sorted 2019, 18, 17) then the second most overall profitable customer grouped that same way and then moving on down the line. Does that make sense?

• ###### 7. Re: Ranking Top Customer Sales Over Three Years

So let me re-iterate:

Based on the data, customer 3 is most valuable.

That's how the customers should be sorted grouped by year?

Does this look right?

• ###### 8. Re: Ranking Top Customer Sales Over Three Years

Hi Chase,

Customer 3 has the most sales total, so Customer 3 is rank one, and so on and so forth.

Used calc:

Total Sales for Customer

{FIXED [Cust Name]: SUM([Sales])}

Added Index as a Discrete Dimension to Rows, set up as follows:

I've attached my workbook for reference.

Thank you,

John

1 of 1 people found this helpful
• ###### 9. Re: Ranking Top Customer Sales Over Three Years

This is a novice question, not a critic, but is there a benefit to introducing the INDEX() function and sorting it by AGG([Total Sales by Customer]) (an additional measure that you created) versus just sorting the Customer on SUM(Sales) which gave the exact same result? I know I grouped it by year and you grouped by customer, but in essence, it's the same result.

I could as easily group by the customer by swapping customer and year pill.

• ###### 10. Re: Ranking Top Customer Sales Over Three Years

Hi Subodh,

Fair question!

What I posted was the first thing that came to mind that I thought might answer Chase's question.  All things being equal, I think having less calculations to mange is generally desirable, so I would probably use your solution if it's accomplishing the same goal.

• ###### 11. Re: Ranking Top Customer Sales Over Three Years

Thank you for your reply. Like I said, I'm new and trying to learn. Appreciate it.

• ###### 12. Re: Ranking Top Customer Sales Over Three Years

Hey John,

That does indeed work, but is just not as visually appealing I suppose. Thanks much for the help here!

• ###### 13. Re: Ranking Top Customer Sales Over Three Years

This does indeed look right. Thanks for checking into this...it is greatly appreciated Subodh!

• ###### 14. Re: Ranking Top Customer Sales Over Three Years

This is actually just what I was looking for here!

1 2 Previous Next