Scatter Charts with Group-By from third data series
This is a basic charting function that falls in the gap between regular Scatter charts and also Pivot Charts. You should be able to plot Y against X while grouping by a third column of categories. This might be helpful in other chart types too, though is particularly vital for scatter charts.
The question was asked here on SuperUser:
I believe it would work better for this functionality to be brought into Pivot Charts in preference to regular charts - regular charts break too easily when data changes, while Pivot charts are quick to set up and have most of the functionality already. All this would require is that:
1) pivot scatter plots can be made from Pivot Tables (or directly from data sources, especially data cubes, perhaps via Flattened Pivot Table).
2) these scatter pivot plots can be of Non-Aggregate data i.e. don't have to choose Min, Max, Sum etc. This makes sense for scatter charts which are very well suited to raw data.
The lack of ability to make non-aggregate pivot scatter plots like these completely counts out Excel (and the PowerX tools) as an analysis tool for data sets that I look at.
Two years later and Excel scatter plots are still absolutely hopeless. I foolishly thought just now I could do some analysis using PowerQuery, create a scatter plot and some Slicers and have the scatter plot update as the data updated. But of course, I forgot, in Excel you still have to manually sort the data and then manually select the ranges you want for each category (series) in order for the scatter plot to make any sense!!
And you still can't draw a scatter plot from a pivot table either. Unbelievable.
Adding a time axis is crucial for a large proportion of scientific analysis. The x-y plots appear to be the only way to do this. It can be worked around manually, but with even moderately large data sets this is time consuming and doesn't allow for changes made in the data to reflect automatically through to the actual charts. Both aggregate and raw data capabilities are needed.
MS Access 2010 can do Scatter Pivot charts contrary to Excel. The pivot chart capabilities were however removed in MS Access 2013.
The use of pivot tables for extracting business data is very powerful particularly when generating line graphs. I deal primarily with scientific data, but this comment also includes visualization of business data.
Most scientific data uses an x-y format so that the data is not skewed by irregular data intervals. Would it be possible generate this type of data/graph via pivot tables or the like without resorting to rather ugly, manual workarounds?
What's the next thing you want to do after plotting some data on a scatter chart? Very often the answer is 'draw some lines with certain equations over it'.
Brett Ables commented
This is the kind of common data visualization task that drives me to other tools like Python, using pandas to collate/slice/group data, and seaborn/matplotlib to plot it. In seaborn, not only can the color be based on another column, but also plots can be faceted with rows and columns of plots where the rows and columns are also based on other columns.
These are also features available with R and ggplot (from which pandas and seaborn get much inspiration).
Great to see this is under review :)
My opinion only, but I think MS should aim to make the PowerX tools suitable for 'Data Science Lite' - it wouldn't require a great many additions to what currently exists. This one is a start.
Btw Scott that SuperUser workaround doesn't work if you don't know how many unique categories your data set has.