Sort by Multiple Avg or Total

3.07K viewsGeneral Discussion


I am trying sort a model by the avg of monthly sales but having some issues. If you see the attached model, you will see why. I have 4 sales regions, with sales for multiple years and months. The avg formula is applied to the monthly sales balances.

It would be nice to do two things. First, within in each region and years, sort from highest to lowest. Second, sort the sales regions by highest sales volume to lowest. This might be done by adding all the sales from each region.

So the final model would show each region from highest volume to lowest. Then within each region, each year, sort the months by highest volume to lowest.

If sorting within years is not doable, then just sorting by region would be OK.



Hi – Thanks for the post:

As you discovered, you can only have one sort going on at the same time in a Matrix.

You can go through a routine to ‘flatten’ out your model. In the attached model file, I created a ‘key’ that took the month, region and year and created an ‘item’ that merged these 3 item values. I am then able to use a USING AS formula statement to bring over the values, and then achieve the sort you were looking for. Take a look at the attached model for the example.