Introduction
Power BI, Microsoft software, enables businesses to visualize and analyze data. It can source data from databases, webpages, and structured files, such as Excel spreadsheets. This blog will use an Excel spreadsheet as the data source to create compelling visualizations with built-in Power BI visuals and publish the report online. The post will also provide tips and an overview of the Power BI user interface for beginners.
Data Acquisition
Acquiring data is the initial step. Power BI provides various options, such as Excel, SQL Server, CSV files, and more, accessible through the “Get Data” icon.
Due to our Excel format, we’ll choose Excel workbook option.
To create the desired dashboard, we must select customer and transaction tables, then click “Load” to load data from Excel spreadsheets.
Power BI user interface
User Interface of Power BI contains 3 main tabs, which are as follows:
- Report
- Data
- Model
The “Report” tab is crucial in dashboard creation, as it hosts the main workspace for designing visuals. Here, we can easily drag and drop various visualizations to craft our dashboard. The end result will mirror the content of the report page.
The “Data” tab presents the tables currently loaded in Power BI for use. We have two tables, “Customer” and “Transaction” which will be utilized in building our dashboard.
The “Model” tab displays the table relationships. We observe a many-to-one relationship between the “Transaction” and “Customer” tables, connected via the “Loyalty Card Number” field.
This signifies that each row in the “Customer” table has a unique loyalty card number, which can be repeated in the “Transaction” table. This aligns with the fact that a customer may make multiple transactions in a year, resulting in multiple entries in the “Transaction” table, while the “Customer” table retains a single record for each unique customer.
Creating your first visual
Power BI shares similarities with PowerPoint in its intuitive drag-and-drop interface. A wide array of visualizations, such as cards, pie charts, tables, and bar charts, are readily accessible in Power BI, simplifying the process of creating engaging visual representations of data.
Let’s create our own visual: A Basic Card.
The initial step is to choose a visual, such as a card visual. Next, we can drag a field from the tables on the right-hand side into the designated fields section. This will automatically populate the card visual, displaying the total sales value of $1.93 million, representing the retail store’s earnings for the specific financial year.
After populating the visual, customization options become available, including color, font size, decimal places for values, and more. Experimenting with these options is the best way to gain familiarity with Power BI’s capabilities and understand the impact of each setting.
DAX formulas
In some cases, calculations in Power BI require more than a simple drag-and-drop approach, such as determining the best-selling product based on product name and quantity sold. This is where Data Analysis Expressions (DAX) comes into play.
DAX, similar to Excel formulas, allows for custom calculations using built-in functions. To illustrate, let’s use DAX to calculate the best-selling product:
Best Seller = TOPN(1, VALUES(‘transaction'[Product Name]),
CALCULATE(SUM(‘transaction'[Product Quantity])))
The formula calculates the sum of product quantity sold for each product, ranks them from highest to lowest, and selects the top result, thereby identifying the best-selling product.
Filters
Now, we will delve into filters, which enable us to selectively display data that meets specific conditions.
In the example provided, we have a table displaying the top 50 customers with the highest recorded sales, which is a common and straightforward use case for filters.
To apply a filter, simply select the table, access the filters pane, and apply a top N filter sorted by the total sales field, as illustrated in the image below.
Slicer in Power BI
A slicer in Power BI is a visual element that enables users to filter data on connected charts. It functions similar to cards and other charts, allowing users to select specific data points.
For example, selecting a product in the slicer will update both the clustered column chart and time series plot to reflect the chosen product’s data.
Slicer interaction affects all visuals on the same report page, unless manually turned off in the format section.
Grouping visuals
Grouping visuals provides dual advantages:
- Keeping the workspace neat enabling easy visual identification.
- Allowing simultaneous hiding or moving of visuals.
When grouping visuals, organizing them based on their position or category on the dashboard, such as summary metrics, customer demographics, and sales breakdown, can facilitate easy reference or modifications in the future.
Report Publishing
Once your report is finalized and to your satisfaction, you can publish and share it. Access the home section, select the publish icon, and follow the prompts for online publication. From there, you can share it with specific user groups and receive feedback.
Final Thoughts
Power BI, without a doubt, is a versatile and powerful tool that simplifies data visualization and analysis, enabling users to gain valuable insights from complex data sets. Its user-friendly interface and rich features make it a top choice for businesses and professionals seeking efficient and effective data-driven decision-making.
- Tags:
- Power BI
- Power BI Visual
