Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Scatter Plot with a Rolling Average Line in Power BI

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 adjust...
Need Help
  • v-venuppu's avatar
    v-venuppu
    1 year ago

    Hi Anonymous ,

    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's avatar
v-venuppu
Icon for Community Support rankCommunity Support
1 year ago

Hi Anonymous ,

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.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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?