Open In App

Power BI – Maps, Scatterplots and Interactive BI Reports

Improve
Improve
Like Article
Like
Save
Share
Report

Prerequisite: Power BI – Timeseries, Aggregation, and Filters 

This article discusses some important concepts used to create interactive dashboards so as to make large business intelligence decisions. We will be discussing the following topics :  

  1. Maps
  2. Scatter plots
  3. Interactive BI Reports


Dataset Used: 
The Excel file ‘AmazingMartGeo‘ contains the 2 datasets-  

  1. ListOfOrders
  2. OrderBreakdown.


On uploading the dataset in Power BI, it gets automatically joined. (You can read about different types of JOINS IN SQL to know the different ways datasets can be joined together). You can download the dataset from here: DATASET. The datasets are joined as shown in Fig 1. 

Fig 1: Power BI: Joins b/w datasets


Maps: 
Using a Power BI map is a great way to visualize data that represent locations. With visually appealing maps and easy-to-understand content, your users will be able to gain more insight into your data.  Before using Maps in Power BI, we need to first understand how to create and work with Hierarchies within the given dataset. 

Steps Involved:  

Step 1 - Select and expand the 'ListOfOrders' dataset from Fields panel.
Step 2 - Select 'state' and 'city' columns one by one and drag and drop them
         into 'country' column.

This will create a new hierarchy column in the dataset. Rename it as 'Geography'.
(As shown in fig 2) 

Fig 2: Power BI: Creating Hierarchies

After creating the Geography hierarchy, follow the steps given below to create a Map in Power BI. 

Steps Involved: 

Step 1 - Select 'Map' from Visualization panel and drag & drop it into report view.
Step 2 - Drag Geography from Fields panel and drop into the Location.
Step 3 - Drag lat and lon from Fields panel and drop into Latitude and Longitude.

(As shown in fig 3)

Fig 3: Power BI: Map



 

If we perform Drilling into this map and expand down levels into the hierarchy, we can we the states and cities involved in the dataset as well. (As shown in Fig 4) 



 

Fig 4: Power BI: Drilling into hierarchies



 


Calculated Columns vs Calculated Measures: 



 

Calculated ColumnsCalculated Measures 
A new column based on calculations done on the existing column. A visual column created when used in a particular visualization. (Dynamically created)
It is created before Aggregation task is done on the dataset.It is created after Aggregation task is done on the dataset.
Once created, it is stored in the dataset. It is not stored in the dataset after creation.
Can be used when need the value for formatting purposes (i.e. slicers, row/column category).Can be used when needed to apply any aggregation to a value (i.e. sum, average etc) that shows a result.

Fig 5: Power BI: Column vs Measure 



 

Now that we understand the major differences between Column and Measure, let us create a calculated measure of our own.  



 

Steps Involved

Step 1 - Go to 'OrderBreakdown' dataset in Fields panel and expand it.
Step 2 - Right click on it and click on 'New Measure'.
Step 3 - Now go the 'Formula Bar' under the Power BI Ribbon and type the following code.
 



ProfitMargin = SUM(OrderBreakdown[Profit]) / SUM(OrderBreakdown[Sales])
 

This will create a calculated measure (ProfitMargin) in the OrderBreakdown dataset. (as shown in Fig 6)

Fig 6: Power BI: Creating calculated measures



 

Scatter Plots:  
A Scatter Chart or Scatter plot is a very useful tool to visualize the relationship between two sets of data. It has two value axes to show- 

  • Horizontal axis: a set of numerical data.
  • Vertical axis: a set of numerical values.
Steps Involved: 

Step 1 - Select the scatter chart from the Visualization panel and drag it into the report view.
 
Step 2 - From the Fields section, expand 'OrderBreakdown' dataset. Then drag and drop
    2.a - Sales to x-axis
    2.b - Profit to y-axis
     2.c - Customer Name to details

(As shown in Fig 7)

Fig 7 : Power BI : Scatter Charts

Step 3 - Now, go to Format --> Data colors --> Default color --> Conditional formatting. 

Step 4 - Do the following : 
    4.a - Set Based on field to ProfitMargin.
     4.b - Set Min/Max colors for ProfitMargin.
     4.c - Press OK.

(As shown in fig 8)

Fig 8 : Power BI : Conditional Formatting

On following the above steps, we get a schematic, color coded Scatter plot. (As shown in Fig 9)

Fig 9 : Power BI: Formatted Scatter Chart


Introduction to an Interactive Business Intelligence Report: 
An Interactive BI Report provides a new way to display your Excel data in a variety of eye-catching, interactive reports. You can add various multiple visualizations to get important information out of the report. Here is an example BI Report containing maps, scatter plots, slicers and donut chart.  

Steps Involved:
Step 1 - Create a new page.
Step 2 - Simply copy your previous visualizations (Map and Scatter plot) and paste it onto the new page.
Step 3 - Drag and drop two slicers from Visualization pane. Then, from the 'ListOfOrders' dataset drag and drop :
    3.a - OrderDate into first slicer.
    3.b - Region into second slicer. 

(As shown in Fig 10)

Fig 10: Power BI: Interactive BI Report

Note: You can always go to the Format panel of each of these visualizations and change its aspects like Text size, color etc. as per your preferences.    
 

Donut Chart:  
A doughnut chart is similar to a pie chart as it shows the relationship of parts to a whole. The only difference is that the centre is blank and allows space for a label or icon.  

Steps Involved:  
Step 1 - Select 2 Donut chart from the Visualization panel and drag it into the Report view.
Step 2 - Select OrderDate from'ListOfOrders' dataset and drag it into the donut chart.
Step 3 - Select Profit from 'ListOfOrders' dataset and drag it into the values section.
 
(as shown in Fig 11) 

Fig 11: Power BI: Donut Chart

Making Interactive BI Reports is an important skill used by businessmen around the world to make important company decisions. Above is one of the examples of making a Report on Power BI. You can use various visualizations. Combine them with filters and slicers to create a business intelligence report of your own. For doubts/queries, comment below.



Last Updated : 17 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads