Simple pivot table issue that I can't solve: how do I consolidate text?

Hi guys!

Let's suppose I have this very simple table:

PivotTextGrouping-OrigTable.JPG

What I want is to get the total revenue of all companies, while keeping their country name. In other words, this is my desired output:

PivotTextGrouping-DesiredTable.JPG

But when I created the pivot table as follows, I ended up with the "count" values of the "Location" column:

PivotTextGrouping-PivotTable.JPG

PivotTextGrouping-Fields.JPG


What do I do to get the desired result (i.e.: the blue table)?

Attached is the Excel file in question...

Thanks!

Hello and welcome to the forum.

Drag Company and Location under ROWS and Revenue under VALUES
Click on the Pivot Table > Design > Report Layout > Show in Tabular Form > Subtotals > Do Not Show Subtotals

See attachment.

That being said, this is not what Pivot Tables are for. I would use Power Query for this.

Here is how to do this through Power Query:

Highlight A1:D5 > Data > From Table > Make sure "My table has headers" is checked > OK
Home > Group By > Advanced > Add grouping > Group by: Company and Location > New column name: whatever > Operation: Sum > Column: Revenue > OK
Drag the headers to reorder if you'd like then Home > Close & Load

See attachment.

Originally Posted by 6StringJazzer

Pivot tables are for doing numeric analysis on data, not for organizing text. There are two options that I can think of (which means there are more options that I cannot think of).

One is to move the Location underneath the Company, instead of making it a column. In your real data, can a single company have more than one location? I expanded your data and your pivot table to illustrate.

Another is to use formulas instead of a pivot table. That is just a bit trickier so I don't want to spend the time on it if the reorganized pivot solves the problem.

Thanks so much 6StringJazzer! Seems like with this solution, I still can't get the location column to appear last, but nonetheless, it's a very decent solution....cheers! :-)

Originally Posted by 63falcondude

Here is how to do this through Power Query:

Highlight A1:D5 > Data > From Table > Make sure "My table has headers" is checked > OK
Home > Group By > Advanced > Add grouping > Group by: Company and Location > New column name: whatever > Operation: Sum > Column: Revenue > OK
Drag the headers to reorder if you'd like then Home > Close & Load

See attachment.

Thanks so much 63falcondude! This is an absolutely brilliant solution.....perfect! I never even knew Power Query exists in Excel (it's unbelievable how many features Excel has!!) Now I'll go ahead and learn more about Power Query

Cheers!

You're welcome. Thanks for the rep!

Power Query is great. All that you have to do when you add new data to the bottom of the table is refresh the Power Query table.