Treating multiple fields as a single pivot table row label and a value source for subtotals in Excel

Question

I have a tabular extract from a DB that has the following fields from orders and order line items:

• order customer (PK1)
• order serial no (PK1)
• order detail 1
• order detail 2
• ...
• order line nr (PK2)
• order line detail 1
• order line detail 2
• ...

I want to use Excel to:

• visually group order lines under their orders
• calculate some subtotals on order lines (for example, by product type, and whole order subtotals)

Is there a way to make pivot table understand that it shouldn't treat each order field as a separate row label, but should treat them as a whole? I could concatenate two PK1 fields into a real order PK and vlookup the remaining fields next to the pivot table, but I have some calculations that I need them for (for example, there's a field "Order max volume" and I want to display the max volume utilization percentage for each order as a subtotal.

I could go back to RDBMS for calculations, but I still need a way to present them visually.

Answer 1

If I understand what you're trying to do correctly then you can do the following:

Create a pivot table from your data. I added Order Number and then Item as rows and Total cost as a sum under Values in the example below.

Next go to the Design tab and use the Report Layout dropdown to change the layout to tabular: