20 Jun 2016

Adding a trend line to a Power BI line chart

Ricardo Santos

To find out more about Concentra's bespoke Analytics Services and Solutions see Services.

At the time this text is being written, there is no way to automatically include a trend line in a Power BI line chart. However, since a trend line is basically a straight line that can be defined by applying linear regression using the existing data points, I'll demonstrate how a trend line can be generated in such charts.

Simple linear regression

Any straight line can be defined by a linear function (x) = α + βx , where α represents the function's result value when it intercepts the vertical y-axis, i.e., when x = 0, and β is the line slope.

Given a set of n data points {(xi,yi), = 1,...,n} , linear regression can be used to find the straight line determined by the linear function as ŷ = â + x, where â and  are calculated to generate the set of ŷ values that make out the "best fitted" straight line to the set of data points.

This is done by performing the following calculations, which minimize the sum of squared residuals of the linear regression model given the set of data points: 

After calculating â and  we can then calculate each trend value for y each value of x.

Building the query for the trend line view

As an example, consider a sales fact table connected to a date dimension table as the following:

Assuming we want to create a trend line for the total sales value () per month for all the sales of the year 2015, to dynamically calculate â and  using SQL against the mentioned sales and date tables we would have:

1. Determining all the values belonging to the x-axis

2. Computing the value of ∑X and ∑X2

3. Get the real total sales value per XValue

4. Computing the value of ∑Y and ∑XY

5. Computing the value of n

6. Computing the value of â and

Putting it all together

So let's put all the previous steps into a view that can be queried directly from Power BI (or whichever reporting tool you're using):

Finally, we can then use the view in Power BI to display the actual total sales values against the trend value:

So in case the reporting tool you're using doesn't allow you to display any trend line against your data, this is one way to do it.

Hope this was useful.

Ricardo Santos

About the author

Lead BI Developer at Concentra