Pivot Chart from 2 sources (possibly 2 pivot tables)

[FishSea]

I'm trying to create a Pivot Chart from 2 different sheets, possibly from 2 pivot tables for convenience. However, whenever I create one from multiple data sources, the field list only includes "Row", "Column", "Value", rather than the actual fields I want. If I try to add page fields during the initial process, it does add data fields but I can't filter down further in the field, other than to include/exclude the entire field or not.

I've included 2 example images of how the charts look (one with just one source, and the other with multiple).

Essentially, in addition to the data source used for the first example, I have another that has spend by customer 'tier', by month. Month and tier are the same across both sources. I'd like to compare the two in the same chart, but can't seem to get it to work properly. (The second one is in bar form, I just hadn't changed it to line at the time).

 

[RoryA]

Unfortunately, creating pivot tables from consolidation ranges (which is what you're doing) is extremely limited. It's generally easier to copy the source data into one table and pivot from that - unless you have a version of Excel that supports the Power BI tools, in which case many more things are possible.

 

[FishSea]

Unfortunately, creating pivot tables from consolidation ranges (which is what you're doing) is extremely limited. It's generally easier to copy the source data into one table and pivot from that - unless you have a version of Excel that supports the Power BI tools, in which case many more things are possible.

That's with what I've gone with (unless I can find some better way), unfortunately I lose some granularity; some of the data doesn't make sense as to how I'd combine it into a single table (and keep individual criteria/values that aren't in both tables), and I'm having to go with totals of that data instead. Guess it still works for a high-level comparison, at least.