Ordering by Multiple Groups in Pivot Table

[MarkJohn51]

Hi there, I wonder if anyone can help me with ordering groups in a Pivot table (Rows area)?

Please see attachment of 2 tabs "my data now" and "what I want".

I want to sort my data, first in ascending order, where a Customer falls into more than 2 or more groups (example if he falls into groups 8 and 27)
Below all of the multiple groupings of 8, 25 and 27, I would the the remainder of the Pivot for the Customer to be to be reported as just in one group (example group 8).

I have outlined in the second tab, how I would want the data sorted.
If this can be done in a Pivot table or a spreadsheet from this Pivot, I am ok with that.
But its worth noting that I cannot change my source data. The Groups were created in my source data.

[JeteMc]

Is this the same question that you are asking in the thread linked below?
https://www.excelforum.com/excel-gen...ml#post5338367

[ORoos]

MarkJohn51,
Would be more helpful if some actual sample data is in the file to see how the raw data is structured.

[flexyourdata]

Use 'Data > Get & Transform > From Table/Range' to create a query on your source data.

In the Power Query editor, add a custom column that categorizes the customer based on the number of groups they're in. This column definition will count the number of rows where 'Group' has the same value as the value in 'Group' on the current row. We can make a quick check to see if it's greater than or equal to 2 and categorize accordingly:

Please Login or Register  to view this content.

Close and load the query into a new sheet, then build the pivot table from the newly loaded table. You can put "category" in your pivot and sort it ascending to get the "2 or more" group at the top, then "group", sorted ascending within "category", then customer within each group.

Note that when your pivot table is in Outline layout (as yours is), you need to right-click on an item from the field you want to sort, then choose the sort order. Do this for each field in the way that works for your goal.

Attached is an example that includes the Query. Select the Green-formatted table on the "query output" sheet then click "Edit" on the Query tab of the ribbon to view the query definition.