Using multiple fields in pivot table rows

[Andrew_Scheibel]

I am trying to create a pivot table to summarize sales data.  It is based on a table that displays "jobs" every row and in each row there is a column for the customers quoted.  This field can have multiple entries (if we quote a job to several different people) and I care about each one.  I want to display for each customer the total quoted amount for all the jobs they fall under.  How can I get a pivot table to recognize each customer (delimited by a comma)?  If I try text to columns and use each column as a field in pivot table rows, it ends up putting each customer as a hierarchical entry under the first field.  I want it to show each customer on the same level and group exact names from contractor 1, contractor 2 etc under one row entry.

[#2]

Hi @Andrew_Scheibel 

Not top clear to me I must admit. I think I know what needs to be done but I'm stuck visualizing how things should be presented on the PivotTable. Could you:

• Post a picture showing what you expect for say 2 customers
• Post (or Share via OneDrive, Google Drive...) an anonymized version of your actual (not a dummy) workbook with say 10 records
• Confirm the version of Excel you run (seems to be 365/Windows)

[#3]

@Andrew_Scheibel 

Based on the information you've provided and the image you sent, here's how you can create a pivot table in Excel to summarize sales data with multiple customer entries per job:

1. Split the "Customers Quoted" Column:

• You don't need to use "Text to Columns" as it would create a hierarchy. Instead, use the following formula in a helper column (e.g., column Q) to split the comma-separated customer names into separate rows:

Excel

=FILTERXML("<x><t>" & SUBSTITUTE(A2, ",", "</t><t>") & "</t></x>", "//t")

• Replace A2 with the cell reference containing the first customer list. Drag the formula down to apply it to all rows.

2. Create the Pivot Table:

• Select the entire data table, including the helper column.
• Go to the "Insert" tab and click "PivotTable."
• Choose the location for the pivot table (new worksheet or existing one).

3. Configure Pivot Table Fields:

• Drag the "Jobs" field to the "Rows" area.
• Drag the "Contractor 1," "Contractor 2," etc. fields to the "Columns" area.
• Drag the helper column (e.g., "Customers Quoted (Split)") to the "Filters" area.

4. Expand All Customers:

• Click the down arrow next to the "Customers Quoted (Split)" field in the Filters area.
• Check the box next to "Select All Items." This ensures all customers are included in the pivot table.

5. (Optional) Calculate Total Quoted Amount:

• In the pivot table, right-click on any value cell and select "Summarize Values" > "Sum." This will calculate the total quoted amount for each combination of job, contractor, and customer.

Explanation:

• The helper column splits the comma-separated customer names into separate rows, allowing each customer to be treated independently in the pivot table.
• Placing the "Jobs" field in the Rows area groups the data by job.
• Placing the contractor fields in the Columns area shows the breakdown by contractor.
• Expanding all items in the "Customers Quoted (Split)" filter ensures all customers are considered in the calculations.
• The optional sum calculation provides the total quoted amount for each combination.

With this approach, your pivot table will display each customer on the same level, grouped by job and contractor, without creating a hierarchy

[#4]

@Lorenzo 

I anonymized the list of what I am working with and threw in what I wish the pivot table to show.  Left some explanations in the file as well.  I do have Microsoft 365 and am working on desktop app.  Open to reformatting if necessary but would also like to know of possible solutions with current format in addition.

[#5 · Accepted solution]

Hi @Andrew_Scheibel 

Thanks for the sharing

• I asked actual Table. In your initial pic. it goes at least until col. X. What you shared goes till col. P
  This isn't a problem IF in what you shared we have the columns we need for the various calc. in the PivotTable - I highlighted them in green (hided the others) according to my understanding
• (In your comments) Open to resolving with a different format for entering customer names
  You can keep recording as you do IF: 1) Customers (on the same row) are always delimited with a comma; 2) SalesRep is always after the 1st opening parenthesis
• In sheet 'Expected' I highlighted some variances + add. comments. On the $ amounts I think I'm right

How from your Table to the PivotTable

This is done with Get & Transform aka Power Query. Basically (all done with the UI only, only renamed the steps so you can easily follow & understand):

• Select required columns only
• Split Customer(s) to new rows on delimiter comma
• Split Customer and SalesRep in 2 columns on 1st opening parenthesis
• Remove [SalesRep]

That's it for now. But this isn't good yet as there's something you didn't say and I couldn't figure out - see below

Total Jobs Won

Currently unresolved/off vs. your expectation. Once we've done the above transformations with Power Query, we get:

Consequently (assuming I understood how to count Jobs Won) in the PivotTable we get:

#1 To update the query - you will have to explain exactly how to identify a Contractor is "linked" to only 1 Customer (and to which one) when we have > 1 Customer on the same row/record (I could make an assumption but I don't like)

#2 Is it possible you have > 1 Contractor on the same row? Something like:

• If you do, how are they delimited (comma again)?
• Cf. #1, how do we map Customers and Contractors?

I'm gonna be off for +/- a week. There are other contributors to this community who can help you further with this

PS: If it was me I would take this opportunity to clean/rename the Table headers... (the query would need to be updated accordingly - easy)

[#6]

So I followed what you said and it turned out almost perfect! As you predicted counting how many jobs won is difficult because the Winning Contractor column is duplicated when it really shouldn't.
So it will NOT be possible to have more than 1 contractor on the same row. How would you construct a query to eliminate duplicate winning contractor entries? In your screenshot, you show Endeavor on 3 lines, ideally it would only be on 1 line: the line with Endeavor as the customer. I can think logically about how this would be done but am not sure the exact syntax required in power query. Thanks!

[#7]

@Andrew_Scheibel 

(I was off). Updated the query to "fix" the [Winning Contractor] as follow:

IF [Customer] = [Winning Contractor] THEN keep the [Winning Contractor] ELSE null

Works for you or do you need something more sophisticated?

[#8]

@Andrew_Scheibel 

Don't know if you have this kind of record:

and if it would make sense to count jobs as follow in the PivotTable:

If it does a way to do it is attached