Open In App

Power BI – Timeseries, Aggregation, and Filters

Improve
Improve
Like Article
Like
Save
Share
Report

Prerequisite: Power BI – Drilling Down and Up in Hierarchies

This article discusses the various important concepts of Power BI along with its implementation. The following concepts will be discussed here :

  1. Time Series
  2. Aggregation
  3. Filters

Understanding the Dataset:
The dataset used is of ‘Long-Term-Unemployment-Stats’. Refer to the dataset to follow along with the below given sections of the article – Dataset

The data is formatted in such a way that with each row we can find the number of Unemployed people of a particular Age group and Gender in present in a particular Period. (Refer to the image of dataset given below.) The structure of the dataset is focused on efficiency in machine understanding rather than human beings.

Snapshot of Dataset used

Creating a Line Chart:
To understand the concept of Time Series, we need to create a line chart for our data in Power BI.

Steps Involved: 

Step 1 - Upload your dataset.
Step 2 - Drag and Drop 'Period' and 'Unemployed' from Fields.
Step 3 - Click on your graph and select Line Chart from the visualization panel. 

Power BI : Line Chart


In the above Line Chart,

  • For every year, we have a separate point representing the number of unemployed people in that year.
  • Each data point represent the aggregate or sum of all unemployed people in that particular year.

Time Series:
Time Series forecasting is a technique used in machine learning, which analyzes data and the sequence of time to predict future events. It is the collection of data at regular intervals in terms of Days, Hours, Months, and Years.

We will implement Time Series using 2 approaches :

Approach 1: Using Show next level drilling.
In this approach, we do not treat the data as a continuous set of points, we treat them as categorical variables.
We take the average of unemployment across different quarters of the year, not the sum.
Show next level is used when we are looking at averages of the time series data to gain valuable information.

Steps Involved: 

Step 1 - Click on your line chart. Go to values section and right click on 'Unemployed' and select Average.
(Doing this will convert your line chart from sum to average as shown in Fig 1)

Step 2 - Go to 'Switch to next level' and press it to drill data points to lower level. 
Example : Average of Unemployed by [Year --> Quarter --> Month] (Shown in Fig 2)

Fig 1 : Conversion from sum to avg of unemployed ( STEP 1 )

Fig 2 : Switch to next level ( STEP 2 )

Here, we observe that following things in the given Time Series:

1. The average unemployment in the third quarter was the highest.
2. The average unemployment in the months of June and July are the lowest.

Approach 2 : Using Expand to next level drilling.
In this approach, we treat the data as a contiguous set of points. (not categorical variables). The data points increases as we drill down to the next level. In a way, it transforms into continuous time-series data.

Steps Involved: 

Step 1 - Convert your 'unemployed' column back to sum from average. 

Step 2 - Step 2 - Go to 'Expand all down one level' and press it to drill data points to lower level. 
Example: Unemployed by [Year --> Quarter --> Month] (Shown in Fig 3)

Fig 3 : Expand all down one level ( STEP 2 )

Aggregation and Granularity:
When you combine values in your data, it’s called Aggregating. The result of that mathematical operation is an aggregate. We use sum, average, min/max etc for aggregating values in the data. On the contrary, Granularity is the segregation of data points. When we increase the rate of aggregation, the rate of granularity decreases.

Steps Involved:

Step 1 - Go to 'Expand all down one level' and press it to drill data points to the month level. (Shown in Fig 4)
Step 2 - Put values like 'Gender' and 'Age' into the legend tab one by one. (Shown in Fig 5)

Using the legends shown above the line chart, observe as the granularity of data increases. 

Fig 4 : Expand all from [Year] –> [Year,Quarter and Month] ( STEP 1 )

Fig 5 : Adding Legends to Line Chart ( STEP 2 )

Stacked Area Chart:
Before understanding the concept of Filters and Slicer, we need to create a stacked area chart.

Steps Involved: 

Step 1 - Select the line chart 'Unemployed by Year, Quarter, Month and Age'. (Shown in Fig 5)
Step 2 - Go to visualization panel and select 'Stacked Area Chart'. (Shown in Fig 6) 

Fig 6 : Power BI : Stacked Area Chart

Filters:
Filters in Power BI are used to sort data and information based on certain selected criteria. Using it, we can extract specific information from our data. For example, we have the stock market data of 5 companies for a week. Using filters, we can choose a particular company and a particular date for which we want to show the data.

Steps Involved: 

Step 1 - Select your Stacked Area Chart Visualization created above.
Step 2 - Drag and Drop 'Gender' column into Filter Pane as shown in Fig 7.
Step 3 - Now you can select between Male and Female or both, for the section of the data you want to display.

Fig 7 : Power BI : Filtering

Slicers:
Slicers in Power BI are ‘on canvas visual filters‘. The slicers, like filters, enable a user to filter the data and view the desired information. Unlike filters, the slicers are present as a visual on the report and let a user select values as they are analyzing the report. Multiple slicers can be created on one page. (As shown in Fig 8)

Steps Involved:

Step 1 - Unselect any visualization. (if selected)
Step 2 - Select 'Slicer' from visualization panel. You will se a blank panel appear in the Report View. 
Step 3 - Select specific data segments from 'Gender' and 'Age' slicer to get the filtered data as shown in Fig 8.  

Fig 8 : Power BI : Slicers

These were some of the important concepts one must be aware of while making reports involving large business intelligence decisions. These tools help make very informative and user-friendly reports. They are extensively used by businesses in many real-world applications. For any doubt/queries, drop a comment below.



Last Updated : 18 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads