Power BI – Create area charts

In this article, we will learn to implement basic area charts using Power BI. This discusses some important concepts used to create the very common area charts so as to make large business intelligence decisions made out of analyzing data trends. We will be discussing the following topics and their implementation in the Power BI desktop.

Area charts

An area chart is a combination of a line chart with an area or layers covered below the line. We could add a secondary y-axis, with just 2 mouse clicks Power BI. The area between the line charts is filled with colors to show volumes of data. We can change the value of the x-axis, y-axis, its title, legends, small multiples, tooltips, etc. We can also add various options like series labels, markers, and data labels in an area chart.

When to use area charts

• To show volume data trends across time series.
• To represent single or dual data series showing the countable dataset.

Follow the steps as given to implement the area charts but for a better look and feel of UI or any other formatting changes, refer to this “Format area charts” article.

Pre-requisite: You can refer to Power BI interactive dashboards for easy implementation of the following charts.

DataSet Used

The dataset used is “DataSet.xls“. Upload the dataset in Power BI and refer to the dataset to follow along with the below-given sections of the article.

Data: We will be working with “DataSet.xls ” data with data fields as shown in the above image. The major variables used to show the charts are as follows.

• Salary: It is the salary of any employee on any particular date.
• Loan: Loan taken by the person.
• Gender: “F” and “M” represents the different genders.
• Remaining_Debt: The remaining debt left out for payment.
• Purpose: The purpose for which the loan has been taken.
• Interest_rate: The interest rate applied for the loan payment.
• Year: The respective year for loan date.
• Quarter: The respective quarter for the loan date.
• Month: Different months in which loan are taken

Load Data in Power BI Desktop

Open Power BI Desktop

Click the “Get Data” option and select “Excel” for data source selection and extraction.

Select the relevant desired file from the folder for data load. In this case, the file is “DataSet.xls”. click the “Load” button once the preview is shown for the Excel data file.

The dashboard is seen once the file is loaded with the “Visualizations” Pane and “Data fields” Pane which stores the different variables of the data file. Drag the red-colored square box to the main canvas.

In the “Visualizations” pane, we can select the chart type needed for our visual representation of business data. In the following image, the red colored square represents the “area” chart that can be dragged for the “Report” view for further process.

Initial Canvas: The initial canvas looks like the following.

Area Charts in Power BI

Click on the “Area chart” (the second one from the above red square box) in the “Visualizations” pane. This creates a chart box in the canvas. Resize after the drag as per the user’s requirement and set options for the x-axis, y-axis, secondary y -axis, legend and other fields as per your preference.

Example 1

Set the Visualization pane: We want to show volumes of data based on quartered time. The vertical axis shows average loan taken by both the genders with average remaining debt left out. Set the visual pane as shown below and notice the output achieved for a better understanding of the working of area chart. Keep hovering on different data points for more details and understanding.

Output: The light-blue colored area chart is shown for females and the dark-blue is for males. This is shown for the second quarter as an example of its working after the fields setting in the Visual pane.

Another Output: Let us analyze for the third quarter by changing the hover on the next data point.

Example 2

By following similar steps, let us analyze another example.

DataSet: In this example, we are taking another dataset namely “SaleData.xlsx“. An idea of the dataset can be taken from the following screenshot.

Follow the similar steps for getting data, navigating the excel sheet and loading in the Power BI desktop. Do the same procedure for drag and drop the area chart in the canvas and set the visual pane to see the area chart.

Changing the setting of visual pane and its fields. Let us take the months in the x-axis. We are showing the sum of sale amount based on different months for the years 2018 and 2019 (set in legends)

Output:

Video output: Keep hovering on various data points to see and analyze datasets in detail for both the year’s total sale amount for items sold.

Example 3

Considering the “DataSet.xls” in the example 1 of this article, we are showing the area chart in a different manner. The loans are being taken for different purposes like “Home”, “Education”, “Travel”, “Personal” and so on. Let us analyze average of loan and average of remaining debt for different purpose based on years (x-axis). In the visual pane, set the “Small multiples” field with “Purpose” so as to show the “Purpose” in different sections of the area chart.

Output: The light-blue area chart is for the average of loan and dark-blue area is for the average of remaining debt for the years shown in the bottom x-axis.

Video output:

Example 4

To get a different view of area chart, set the fields as given below by drag and drop from the data pane.

Output:

Change the visual pane setting of x-axis to the following and see the effect. The x-axis is changed to “Year” along with the “Date Month” option. The user can change the fields and see the affect as per the requirement.

Output:

Using Filters and Highlights in area charts

We are taking the above output as an example to use filters on the area chart.

Make use of the filter pane in the left side of the “Visualizations” pane.

According to the user need, one can use various “And” and “Or” conditions while using filters along with “ascending” or “descending” sorting options.

The following shows the result of area chart on using filters of “Average Salary is less than Rs.80000” AND “greater than Rs.55000”. It will show the output result fullfilling the conditions set by the Filters pane.

Similarly another filter is set in the pane which selects the years which are ticked marked as given below. The area charts are created which fulfills the conditions set in the filter pane for the selected years.

Cross filters

To highlight any one particular area in the chart, select that area or its top border. If there are other visualizations on the same page, highlighting (highlighted blue colored column) a basic area chart does not cross-filter the other visualizations on the report page.

Consider the following area chart of “Average of Loan by Year and Purpose”.

To the above area chart, the user can just highlight or filter any specific concentrated part for more detailed view.It can be implemented in the same “Report view” page by dragging other fields for filters or selections. We can select any element and just notice the effect on the area chart it gets cross-filtered.

You can select any element like any “Quarter”, any gender like “Female” or “Male” or any other value range like “Average of Salary” or “Average of remaining Debt” and so on. The following shows the output for the third quarter and details for “Female” gender.

cross filter: The following shows details for second and fourth quarter for “female” gender with other value ranges.

Video output:

Considerations

• It works well with 2 or different areas.
• For trends more than 3 measures, its better to use line charts.
• Measures more than 3 needs treemap.