This might be a good opportunity to use the Rank() Calculation as a means of sorting. Given that your weekly difference is already a table calculation, using more table calculation is best in this use case; i created a few more to help with this rank/sort:
- [Weekly Difference] = ZN(SUM([Mileage])) - LOOKUP(ZN(SUM([Mileage])), -1) //this is just your weekly difference calculation saved off so that it can be referenced (see: saving and customizing table calculations)
- [Sum of Weekly Difference]=WINDOW_SUM([Weekly Difference]) //Sum of the difference across all weeks for each vendor
- [Rank by Sum of Weekly Difference] = RANK([Sum of Weekly Difference]) //Ranking based off the prior sum.
I've attached the original workbook with the solution implemented.
mileage.twbx 54.2 KB
That was indeed helpful. I had an idea about window_sum but your solution really helped to crack it.
Appreciate your time on the same.
I was having a problem of ranking/sorting according to sum of differences across weeks which was a calculated field in itself. So sorting by sum would not be beneficial here.
Thanks for your response.