Scatter Plot with a Rolling Average Line in Power BI

[AthanSkal]

Hi all,

I'm trying to recreate a chart similar to the one in the image below. 

Currently I have just stacked 2 different plots one on top of each other, with transparent background and adjusted Y,X limits.

Any idea how can I do this in one PowerBI plot (not using Python/R)?

Thank you!

[v-venuppu]

Hi @AthanSkal ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @AthanSkal ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @AthanSkal ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

Hi @AthanSkal ,

You can use a Line and Scatter Chart (or Combo Chart) to overlay:

Scatter plot of raw X vs Y values
Line plot of average Y per X-bin

Here are few Steps to Implement:
Create Bins for X-Axis (In your case bins are already created).

Right-click your X-axis field - New Group
Choose Bin Type: Number of Bins (e.g., 10)
Create a Measure for Average Y per Bin:

AvgY_PerBin = AVERAGE('Table'[Y])

Create a Line and Scatter Chart or Combo chart:
X-Axis: X (for scatter) and X_bin (for line)
Y-Axis:
Raw values: Y (scatter)
AvgY_PerBin

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @AthanSkal ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

Yes, you can create a scatter plot with a rolling average line in Power BI without using Python or R and without stacking visuals. Here's how to do it in one Power BI chart using built-in features and DAX:

Use a Line and Scatter Chart in Power BI:

Plot the original data as scatter.

Add the rolling average as a line.

Here are some steps to follow:

1. Create a Rolling Average Measure in DAX

Suppose your data table is called Complaints, and you have:

Date column (or X-axis numeric value)

Value column (Y-axis)

Create a rolling average measure (7-day example):

RollingAvg_7Day =
AVERAGEX(
DATESINPERIOD(
'Complaints'[Date],
MAX('Complaints'[Date]),
-7,
DAY
),
CALCULATE(SUM('Complaints'[Value]))
)

Adjust -7, DAY to change the rolling window.

2. Use Line and Scatter Chart Visual

1.Go to Visualizations - Choose "Line and Clustered Column Chart".

If using "Line and Scatter" is not available, use Combo Chart, or plot with dual axes.

2.Assign:

X-Axis: Date (or numeric X-axis)

Y-Axis (Line Values): RollingAvg_7Day

Y-Axis (Scatter or Column Values): Value (raw data)

3. Tweak Formatting
Make the scatter or bar points small/light blue.

Make the line bold or a different color.

Turn off secondary Y-axis if both metrics share the same scale.

Adjust gridlines and axis settings for aesthetics.

You will see final output as follow:

• Raw scatter (or column) points.
• A smooth rolling average line on top - in one chart.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 Thank you.

 

[AthanSkal]

Hi,

Thank you for your feedback.

Your approach is clear and straightforward. However, in my case, I'm not exactly aiming to display a rolling average. Instead, I want to show the average value per bin of my X-axis.

Specifically, for plotting the line I have done the following steps:

1. I have caclulated the bins ( By choosing my Field -> Right Click -> New Group ->Group Type:Bin -> Bin Type: Number of bins).
2. I have ploted the Average of Value (Y-axis) versus Bins (X-axis)

For plotting the scatter:

1. I have just plot the Average of Value versus the raw X-axis values 

Basically, the problem occurs because I am trying to have 2 X-axis (the raw version & only the bins), while the Y axis is the average of Value.

Got any thoughts/recommendations on this?

[lbendlin]

Is Deneb an option?