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.

Solution

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:

Comments

[CLockeWork]

It depends on how the data is set up. Do you have a column for each of those values, or a row for each order with a PK at the begining of the row?

[Alexey]

I have a row for each order line with order data repeated for each line in the leftmost fields.

[Alexey]

No, not quite. The problem is there are multiple fields related to the order itself I need displayed and the order key is not a single field, but a pair of fields.

[CLockeWork]

Order key can be combined by adding a helper column =[KeyPrt1]&[KeyPrt2] Can you give me a bit more info about the order you need displayed?