Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Analytics with Power BI Desktop for Trend Analysis


By:   |   Last Updated: 2017-10-16   |   Comments   |   Related Tips: More > Power BI

Problem

In the last tip, we learned how to forecast on time series based data using Power BI Desktop. Another important aspect of analyze time based data is finding trends. From a reporting perspective, a trend may be just a smooth lasso curve on the data points or just a line chart connection data points spread over time. From an analytics perspective the trend can have different interpretations. In this tip, we will learn how to analyze trends in data from an analytics perspective using Power BI Desktop.

Solution

Power BI provides options to draw a trend line for visualizations using scatter plot charts and line charts.

First, we need data to populate a visualization on which we will create reference lines. I recommend installing the AdventureWorks sample database, which has a number of tables to populate visualizations with sample data. Assuming that the sample data is available, open the Power BI Desktop and click on the Get Data menu. We need to connect to the data source where you have the sample data and import the same. In this tip, we will be using the FactInternetSales table from the AdventureWorks database by following the steps mentioned below.

Click on the Get Data menu in the Power BI user interface as shown below. This should bring up options to select from a list of different data sources. In this case, the data is hosted in SQL Server in the AdventureWorks database. Therefore, we will select SQL Server as the data source.

Get Data in PowerBI - Description: Menu

After selecting the data source, the next step is to provide connection details / authentication credentials as shown below. Provide the server name and optionally the database name on which the data is hosted. It is recommended to provide the database name as the connecting string to SQL Server needs to be part of the connection. The next option is Data Connectivity mode for which there are two options. Either we can import data into the Power BI or the data can be read directly from the data source while the report is being processed. There are certain options Power BI options that require data in a specific format to enable analytics related features. So consider selecting the Import option for now.

SQL Server Database Connection in PowerBI - Description: DB Connection

After you select the same, you will be presented with a list of different tables and views hosted in the database to which the connection has been made. Select and load the FactInternetSales table as shown below.

PowerBI Data Table - Description: Data Table

One standard method of analyzing two numerical values on a graph is by using scatterplot graph. In a scatterplot, each value has an X-axis and Y-axis and is plotted on the graph using the values of two scales. We can use the fields as Average of UnitPrice and Average of SalesAmount. We also want to see this comparison over time, so select the OrderDate field in the Details section. The reason for selecting the same is that we want to project data over time. So select the scatterplot icon from the visualizations pane and create a blank scatterplot graph on the report layout. Select this blank graph, and add the fields as discussed above. This will create a scatterplot chart of average of unit price vs average of sales amount over time.

PowerBI scatterplot chart of average of unit price vs average of sales amount over time - Description: Trend Lines

Here the chart seems to show linear relationship as the points seems to be organized in a straight linear line, but we cannot be sure just by reviewing visually. The chart seems to show a series of points that are closely overlaid near or on the top of each other. We need an explicit indicator of the same, like a project trend line on this data. Power BI provides options to draw a trend line by studying the data in the graph. To accomplish the same, click on the analytics pane, and you should find a trend-line option as shown above. Click on Add to create a new trend line. You can format the different options as shown below.

Trend Line 1 Properties in PowerBI - Description: Trend Lines

After adding the trend line, the graph should look as shown below. This looks very trivial as we can create a trend using a line chart. However, this trend is more like a linear trend line used in linear regression methods where the best-fit line passes through the minimum of squares distance / variance from all the points in the plot. This may sound statistical and may be confusing too. However, here you do not have to learn the linear regression formula to find best-fit line and draw trends, so do not bother about the statistics behind it. If you are interested in linear regression, you may want to refer the Machine Learning Tutorial where statistics and linear regression using SQL Server and R have been discussed.

Positive Trend Displayed in PowerBI - Description: Positive Trend

Now let's try a different aggregation to look at a different trend. Instead of average of Sales Amount, change the aggregation to Sum of Sales Amount. To change the aggregation, you just need to right-click on the field and select the aggregation of choice from the menu as shown below.

PowerBI Aggregation Options - Description: Menu

After making the change, the trend would look as shown below. This shows that the trend is negative. As the average of unit price decreases, the sum of sales amount increases.

Negative Trend in PowerBI because As the average of unit price decreases, the sum of sales amount increases - Description: Negative Trend

From this limited trend analysis, without looking at the data, one can make an initial assumption that as the average of unit price of products increases, the sum total of overall sales decreases, but the average of sales increases. This indicates that for expensive products the total sales is low. As the number of products sold are less and the unit price is high, the average keeps on increasing shown a linear positive trend. In this way, trend lines enables quick interpretation of data using different aggregations with trend lines.

In the next part of the Power BI Analytics tip series, we would learn how to classify data in clusters using Power BI Desktop.

Next Steps
  • Try applying trends on your data of interest to make high-level derivations without looking at individual data transactions.


Last Updated: 2017-10-16


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools