How to add more series in a Power BI Scatter Chart

[Fern_21]

Hi all!

In a Power BI scatter chart, I have to represent 3 series. I have filters that determine whether a project is closed or open.

Therefore I must represent in a gray series the revisions for the closed projects (without being influenced by the filter), a blue series for the closed projects which must change based on the selected filter, a red series with the open ones. Currently, I've only managed to insert one series.

How can I do? Thanks

[Shahid12523]

Create a category column with DAX:

SeriesCategory =
SWITCH (
TRUE(),
'Projects'[Status] = "Closed" && 'Projects'[IgnoreFilter] = 1, "Closed - Static",
'Projects'[Status] = "Closed", "Closed - Filtered",
'Projects'[Status] = "Open", "Open"
)

Put this column in the Legend field of the scatter chart.

Format colors: Gray = closed static, Blue = closed filtered, Red = open.

This way, you’ll get all 3 series in one scatter chart. ✅

[Fern_21]

Hi @Shahid12523 !

In addition to the 3 series, I should also represent trend lines. We would like a trend line in black for all projects and a trend line in blue for the filtered projects. The open projects should be in red and should not influence the trend lines. Also, my x and y axis is dynamic. I have parameters that allow me to select multiple fields.

It is possible in a scatter chart?

[v-sshirivolu]

Hi @Fern_21 ,
Here are options you can consider:

Option 1: Custom Visual

Utilize Deneb (Vega-Lite) or Charticulator.

Plot three scatter point series.

Add explicit regression lines:

Black: regression for all closed projects.

Blue: regression for filtered closed projects.

Exclude “Open” projects from regression analysis.

Dynamic axes and custom trend lines are supported by binding axes to field parameters.

Option 2: Manual Regression with DAX

Create measures to calculate regression slope and intercept using LINESTX.

Repeat the process for the filtered closed projects.

Generate a calculated table for the regression lines and combine both (black and blue) into a single table.

Build a scatter chart for data points and overlay regression lines using Deneb or an axis synchronization method.

Option 3: Split Visuals

Present a scatter plot for points only.

Add two separate line charts for the black and blue trend lines.

Synchronize X and Y axes.

Arrange the visuals to resemble a combined chart.

Example DAX for regression:
RegressionPoints_Closed =
ADDCOLUMNS (
GENERATESERIES ( MIN('Projects'[X_Value]), MAX('Projects'[X_Value]), 0.1 ),
"Y", [Slope_Closed] * [Value] + [Intercept_Closed],
"LineType", "Closed Trend"
)

Slope_Closed =
VAR DataPoints =
FILTER ( 'Projects', 'Projects'[Status] = "Closed" )
RETURN
LINESTX ( DataPoints, [X_Value], [Y_Value] ).[Slope]

Intercept_Closed =
VAR DataPoints =
FILTER ( 'Projects', 'Projects'[Status] = "Closed" )
RETURN
LINESTX ( DataPoints, [X_Value], [Y_Value] ).[Intercept]

[MFelix]

Hi @Fern_21 ,

This depends on how you model is setup and how you make the difference between the projects, are you using meausures? is there a column with classification?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Fern_21]

Hi @MFelix !

Yes, use of measures to distinguish between open or closed projects. I could also put a flag.

I'm currently experimenting with the Deneb graph.

[MFelix]

Hi @Fern_21 ,

Have you tried creating the measure to get the values placed on the scatter plot  and then use the measure to create a condittional formatting for the color? Based on the filter of each project?