Nowadays, many companies use Power BI to gain insightful business information from data. The business intelligence tool enables us to visualize data. The powerful tool can turn raw data into easy-to-understand visuals. It enables organizations to make decisions for the future. Compared to programming languages, it is quite easy to learn. No coding skills are required. At Kaggle, I found a sample database of a fictional company, Adventure Works. Adventure Works is a “made-up” company that sells bikes, accessories, and clothing. Let’s take a look at some company data with easy-to-understand visuals.

Database tables

The starting point is loading the data into Power BI. You can get data from various resources, for example, MS Excel files, CSV files, the web, or a data warehouse. I download the sample database from Kaggle; the sample database consists of 10 separate CSV files. I load the CSV files into Power BI. When loading the files into Power BI, the data from the CSV files is visible in the “table view”. Some tables are redundant, for example, there are 3 separate files including sales data for 2015, 2016, and 2017. All columns are the same. I query the data into 1 table. Also, the product categories and product subcategories are stored in separate tables. As in other programming languages, there are multiple types of joins: left outer join, right outer join, inner join, and full outer join. I join the tables by choosing the “left outer join”. All entries from the subcategory table are kept and the matching main product category are added to the table.

Data modeling in Power bi

Data modeling is the most important thing for the foundation of your Power BI report. Here, you connect data points with each other. For each relationship, you select the columns in the tables that are related, for example, the customer key/ ID. Each relationship has a cardinality. The sales tables with multiple order lines has a column with a customer key in it. The customer table contains rows with data about a unique customer. Each customer has a unique customer ID. The cardinality of this relationship is many-to-one. It is easy to find patterns in your data with the data points connected. For example, each product belongs to a product category. A product category is divided into subcategories. Bikes, accessories, and clothing are sold in various countries. From here, you can derive the number of bike sales in each country, etc.

The Dax Formula

The DAX formula stands for ‘Data Analysis Expressions’. It is not a programming language, but it is a formula language that allows users to define calculations in calculated columns and calculated fields. It helps to create new information from the existing data in the data model.

For example, I calculate the profit or the costs of returns of Adventure Works. In the sales table, I look up the product prices and product costs from the product table. I multiply these values by the quantity ordered. The profit is the sales minus the costs. I calculate the sales, costs, and profit for each sales order line within the table. Adding a new column to a table and using a formula is quite static, and the calculation is based on existing data. To calculate the return costs per return, I could do the same.

Sales = LOOKUPVALUE(AdventureWorks_Products[ProductPrice],AdventureWorks_Products[ProductKey],AdventureWorks_Sales[ProductKey]) * AdventureWorks_Sales[OrderQuantity]

Costs = LOOKUPVALUE(AdventureWorks_Products[ProductCost],AdventureWorks_Products[ProductKey],AdventureWorks_Sales[ProductKey]) * AdventureWorks_Sales[OrderQuantity]

ProfitLine = AdventureWorks_Sales[Sales] - AdventureWorks_Sales[Costs]

ReturnCosts = LOOKUPVALUE(AdventureWorks_Products[ProductCost],AdventureWorks_Products[ProductKey],AdventureWorks_Returns[ProductKey]) * AdventureWorks_Returns[ReturnQuantity]

A measure is a formula based on an aggregation. For example, I would like to know the average customer sales and profit. I divide the sales or profit by the unique number of customers.

AverageCustomerSales = SUM(AdventureWorks_Sales[Sales])/COUNT(AdventureWorks_Customers[CustomerKey])

AverageCustomerProfit = SUM(AdventureWorks_Sales[ProfitLine])/COUNT(AdventureWorks_Customers[CustomerKey])

Power BI Dashboard

This is the Power BI dashboard I created for the sales, returns, and profits of Adventure Works. The total order quantity in item numbers was 84K in 2015, 2016, and 2017. The United States has the largest sales and profit, followed by Australia. The bike accessories category has the largest sales quantity. The tires and tubes subcategory has the largest return quantity, whereas road bikes have the highest costs of return.

You can filter the dashboard either by using the slicers or by clicking the country in the visual. I filter the data for May 2017. Still, the United States has the largest bike sales. Summer is on its way in May; seasonal influences may affect the number of bike sales.