Pivot Table to Analyze Combinations of "Sum of..." Values

So I have a simple enough pivot table that plots "Sum of..." billing information over time. There are multiple tasks, so the plot has multiple lines. Basically, I've achieved the following with my pivot chart: https://excel-example.com/other-tuto...ble-line-chart

Final-Line-Chart.jpg

Referring to that link / the image of the final product where there are separate lines (separate "Series"? Is that the right terminology?), I want to be able to play around with my pivot chart and look at combined lines. So maybe I want to consider "Hans"+"Jack" lines; or "Tom"+"Hans".

The least convenient solution is to go into the data table and start renaming the Series data entries to some common name (e.g. Find and Replace all "Hans" and "Tom" entries to "Hans+Tom"). That's not a very dynamic solution...

However, this is another case where I believe someone else has already thought of this -- and in fact it must be a common problem... I just haven't had luck searching for the right blog that details how to solve it! Can someone point me in the right direction?

Originally Posted by Richard Buttrey

Do you mean you want to add the values for various series, or just show any two series rather than three. If the latter then using a Slicer with your Pivot Table would do that.

Otherwise upload the workbook so that we can see in context.

To Alan: Fair point. But as long as the image is showing in my post, actually there's nothing needed in that link.

To Richard: I want to combine, not just filter. I've had some success in the last few minutes, using Group..., but Grouping seems to get really complicated in my dataset. Either you have a suggestion that isn't the Group feature, or you can comment on Groups being complicated - - - perhaps a blog that helps to demystify it.

Unfortunately, I would have a lot of work to anonymise my real workbook, so I can't share that - - so that may be an impediment to further advice if your answer is going to be "Use Groups, they're easy!", because I'm currently using Groups and they're hard Mainly, I create a group and I have to play with filters to get the original two Series off the chart, and to show the combined series instead...

I began having a go at anonymising the data -- it'd be too much work in my case. But to be clear, I really never hoped for anyone to give me the exact solution to the problem. I've had lots of success on this forum just describing the problem, and getting pointed to helpful blogs that discuss how to achieve what I'm looking for.

At this point, I've proceeded with using the Group feature to select multiple Series items (referring back to that original graph image I showed, I am effectively adding together the Tom and Hans series of data).

The challenge I'm currently trying to resolve is understanding what's happening when two Series are combined.

1. When you Group two Series (e.g. Tom + Hans), it creates a new PivotChart Field, and that field is added to the "Legend (Series)".

2. a. So once again, referring to the first image I had attached, I would have put a field called "Names" as my "Legend (Series)"; and a field called "Numbers" in "Values".
2. b. then when I group any names together (e.g. "Tom+Hans") then it creates a new Field, default named "Names2" and puts that field in "Legend (Series)".
3. However, the plot looks exactly the same as before. The three names are plotted as separate series still... There are two sets of Legend (Series) filters: Names and Names2. Names lists the names separately. Names2 lists Jack and Tom+Hans.
4. If I remove the field "Names" from the "Legend (Series)", leaving only Names 2, then I get what I wanted -- Yay! Just two series are plotted -- Jack and Tom+Hans.

I need to understand the above, because my "nice to have" step is:

5. I want one filter that lets me plot all three names (Tom, Hans, Jack) separately AND Tom+Hans.

Again -- really sorry I cannot provide the anonymised data set. I'm mainly hoping the image I provided, and the simplicity of the description, inspires some understanding of a common problem that someone's faced before; and from there, I can get a link to some useful blog. I'm not looking for the exact steps in the context of my data set.

The answer turned out to be adding Calculated Items, instead of Groups. Groups were a bit of a red herring here.

So the final answer, to analyze combinations of series, is:

You may use either Groups or Calculated Items; however, if you want to show your separated items and/or the combination, you have to use Calculated Items. Calcualted Items will also just be a lot easier to understand. You will effectively just add a new series to your list of Series being plotted. Groups create new fields, and it's really messy.

I'm sure there's a lot more depth to the difference between Groups and Calculated Items. I did a brief search to find any blogs describing the difference -- not much luck. I would give credit to any follow-up poster who has such a link or explanation for us.