See attached workbook.
First graph shows Top 10 Cities for 2014 with comparison of these top 10 over the past years
The second graph shows top 10 cities for 2014 and comparison of them against all cities in past year.
One of these should answer your question.
Reach out for further assistance if required.
Trend Graph by Rank.twbx 1.2 MB
Thanks for the quick reply Khalid. I'll see if I can apply similar principles to our dataset and achieve the result I'm after.
1 of 1 people found this helpful
Let me know if I can assist further in anyway and if you find the workbook helpful please can you mark the reply as helpful.
I've had a look, and feel that this isn't the solution I need. What I need is to be able to show "What Top N products generated the highest sales in the specified month, and what was their SUM(Sales) in each of the prior N months?" Maybe I didn't make that clear enough in the opening post.
The solution provided effectively just says "Your top N products by SUM(Sales) in the last month were these, and they rank against each other like this in the preceding months". I don't need this - all I want rank to do is determine what the top selling products are in the specified month. What I need to see is the historic SUM(Sales) for the Top N products who made up the Top N in a specified month.
So, imagine Product A generates the most Sales in April 2016 and I only want to see the Top 1, so Product A is in scope. I need to see the SUM(Sales) for Product A in April 2016, and then the preceding N months of SUM(Sales) for Product A.
If I want to see the Top 2 and Product A and Product B are then in scope, I don't want to see them ranked against each other, I just want a line chart which depicts the relative SUM(Sales) of those in scope products. It's the SUM(Sales) that matters in terms of the chart, not the Rank. Rank merely determines what products are in scope.