SSRS row sorting based on column group values

Question

So, I have a tablix that I want to be able to sort the row group by the values in the column groups. The kicker is that I want it to sort by the greatest value in the greatest column grouping first and when the sorting for the greatest column group has come to an empty row then I want it to go to the next column group and sort the rest again until it goes through all of the column groups. The other kicker is that I want it to be dynamic so that when the year rolls over I won't have to change the report for the years that will show.

For example my tablix has customers in the rows and years across the top and shows revenue per year and I'd like the results to sort by the value from the latest year first then sort by the values in the second greatest year and so on until it sorts the whole tablix. I also need it to be dynamic so that I don't have to change it when the year rolls over to the next year.

I've tried to add my year to the Sum function I used to sort it, but that didn't work.

Answer 1

I was able to sort my report by counting the distinct values in the column groups first and then by the sum of the values in the report.

Edit: Here's my solution: Season is my column group and Net Revenue is the value I wanted to sort on to get the values that have multiple years on their contract to the top.