sort by table group record count

[antproberts]

I have a table that I have grouped by registrtion number this table then shows a number next to the registration which is the count of how many times it appears in the table.  My question is how can I get the table to sort by this number either ascending or desending?  See below image.

[WillowMobileSys · Accepted solution]

Re-organizing Groups is a bit of a pain point.  We cannot re-order the Group Headings by other columns.  So, in order to sort the Groupings by the aggregate value, you not only will need to have those aggregate values on each row BUT you will also need to make them part of the Group Header.  

This is what I would do:

1)   Add a virtual column to table and assign it an expression that calculates the aggregate value.

2)  Add a second virtual column that computes a Group Header = Aggregate Value + Original Group Header name.  Note: you could combine 1) and 2) together if you prefer.  It might be more performant.

3)  Use the new "Group Header" column as your grouping column sorted in descending order.

Be aware that with this arrangement, the Grouping order is highly dependant on row counts.  If rows are being added frequently, you will see the Grouping order changing a lot after each Sync.

[WillowMobileSys · Accepted solution]

Re-organizing Groups is a bit of a pain point.  We cannot re-order the Group Headings by other columns.  So, in order to sort the Groupings by the aggregate value, you not only will need to have those aggregate values on each row BUT you will also need to make them part of the Group Header.  

This is what I would do:

1)   Add a virtual column to table and assign it an expression that calculates the aggregate value.

2)  Add a second virtual column that computes a Group Header = Aggregate Value + Original Group Header name.  Note: you could combine 1) and 2) together if you prefer.  It might be more performant.

3)  Use the new "Group Header" column as your grouping column sorted in descending order.

Be aware that with this arrangement, the Grouping order is highly dependant on row counts.  If rows are being added frequently, you will see the Grouping order changing a lot after each Sync.

[antproberts]

Thanks , that kind of makes sense what expression would you suggest for the aggreate value?

[WillowMobileSys · Accepted solution]

It's the same as the one used in your Grouping.  It appears it a row count of the rows containing your current Group Heading.  So the expression would be something like:

COUNT(FILTER("Table Name Here", [Group Header column] = [_THISROW].[Group Header column]))