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

microsoft excelpivot table

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.

Best Answer

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:

Tabular pivot

Related Solutions

Excel – Referencing multiple fields and items from a single cell – GETPIVOTDATA

The official Excel documentation states the syntax of GETPIVOTDATA is:

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

However there is an alternative and more flexible way of using the GETPIVOTDATA which is not documented:

GETPIVOTDATA(pivot_table,"'Sum of " & data_field & "' '" & item1 & 
     "' '" & item2 & "' '" & ... & "'")

Where Sum of could be replaced by other aggregation types.

The solution to your problem is therefore:

GETPIVOTDATA(
  PTSALES,
  "'Sum of Sales' " &
  "'" & Manager & "' " &
  if(len(Team>0),"'" & Team & "' ","") &
  if(len(Agent>0),"'" & Agent & '","")
)

And make sure that the named range Team and Agent are empty in case you want to have to aggregate over them. For clarity you might want to move the if statements to a separate cell

Excel – How to add custom columns to Pivot Table (similar to Grand Total)

What you need is not a calculated field, but a calculate item.

Your column field has 12 items, namely the 12 months of a year. What you want is an additional column called Avg. So we need to add Avg besides the 12 items:

Activate (click on) any cell with a label of the column field, say the cell with Jan, then PivotTable tools -> Options -> Fields, Items & Sets -> Calculated Item (it is dimmed if you do not activate a cell for field labels.) Then, name a new item Avg and set the formula =AVERAGE(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

Add other calculated items similarly.

Best Answer

Related Solutions

Excel – Referencing multiple fields and items from a single cell – GETPIVOTDATA

Excel – How to add custom columns to Pivot Table (similar to Grand Total)

Related Question