Line Chart Aggregated Value on X-Axis (Or Multiple Series on Scatter Chart)

[Anonymous]

Hello!

I'm hoping that I am overlooking a very simple solution to my problem. I have created a scatter-chart that has a measure for the Y-Axis and the AVERAGE of a column for the X-Axis. This works perfectly, however, I need to show multiple series on the scatter chart. These series will have different filter contexts so I can't just specify a column in the "Shapes" or "Size" wells of the scatter chart.

You can, however, show multiple series on a line chart. But a line chart does not allow you to use an aggregate on the line chart so I cannot use the AVERAGE of my column.

Does anyone know a way that I can show either multiple series for a scatter chart or an aggregated value on the X-Axis of a chart that allows multiple series (i.e. line chart)?

Thanks!

Parker

[Anonymous · Accepted solution]

Answering my own question as I think I have exhausted all of my options.

Line chart with aggregated value on X-Axis:

-Not possible

Scatter Chart with Multiple Series:

-There is no visual with this functionality. I imported every scatter/bubble/dot plot visual from the store and they all only allow 1 Y-Value

-You can, however, code around this. Here are the steps to accomplish it.

1. Duplicate your data and create a column identifier (Your table should have the same data twice, once for each category).
2. Create a measure that specifies different calculations depending on the column identifier.
3. Add the new measure to the Values well of the scatter plot and add the column identifier as the legend.

Hopefully someone can benefit from this approach as I believe it is the only options as of yet.

-Parker

[v-frfei-msft]

Hi @Anonymous,

I made one sample here. 

1. Enter the sample data like this.

2. Create a measure using the formula.

Measure = CALCULATE(SUM(Table1[average]),ALLEXCEPT(Table1,Table1[series]))

Then we can get the result as the picture.

For more details, please check the pbix as attached. If it doesn’t meet your requirement, kindly share your sample data and excepted result to me.

https://www.dropbox.com/s/wf2rkouhspfftkv/Line%20chart.pbix?dl=0

Regards,

Frank

[Anonymous]

Thank you for the response @v-frfei-msft, however, I don't think this will work as a solution. I see that you are adding "series" to the legend. I need to be able to add multiple measures to the Y-Axis to create multiple series, similar to a line chart.

Thanks you,

Parker