Summary
Highlights
This section introduces the webinar, outlining the plan to build two dashboards using Power BI Desktop, Power Query, and Power Pivot. It then details the sales data from two Australian retail chains (Ready Wear and Belling's) covering 2016-2017, highlighting the different lookup tables (financial periods, postcodes, buyers, managers) and how Power BI's modeling tools differ from Excel's VLOOKUP approach for handling relational data efficiently.
The process of importing data from an Excel file into Power BI Desktop using Power Query is demonstrated. It showcases various data sources available and the importance of having data formatted as Excel tables for easy import. The Power Query Editor is used to check data types, and custom columns are added to the sales table to calculate total sales, costs, and gross profit. The steps are recorded, ensuring automatic updates with new data.
Once data is loaded, the video explores the three main views in Power BI Desktop: Report, Data, and Relationship. It demonstrates creating a relationship between the sales and dates tables to classify sales data by fiscal periods. The importance of data types and preventing inappropriate aggregation (e.g., postcodes) is covered, along with formatting currency fields for consistent visualization.
The first dashboard, a business summary, is built. It includes a KPI for total sales over time, a clustered column chart for sales by state and chain, and a combined column and line chart for sales and gross margin by financial year quarters. A pie chart for sales by chain and a filled map visualizing sales by state are also added. Data ambiguity for states on the map is addressed by adding a concatenated state and country column.
A bubble chart showing sales and gross profit by category across time is created, requiring a new measure for gross profit percentage using DAX. A slicer for filtering by state is added and formatted. The powerful cross-filtering and highlighting capabilities of Power BI visualizations are demonstrated, explaining the difference between slicer filters (additive) and cross-filtering (non-additive).
A second dashboard focused on regions and chains is developed. It features a clustered bar chart for sales by buyer, and a globe map for sales by postcode. Similar to the first dashboard, data ambiguity for postcodes is resolved using a new concatenated column including postcode, suburb, and country. A bar chart showing sales and gross profit by state and manager, with color saturation for gross profit, is also built.
An area chart displaying sales over time for different chains is added, along with a multi-row card for high-level sales, gross profit, and gross profit percentage. A custom sparkline visualization is imported and used to show sales trends by state, aligning with the state slicer. The process of refreshing the Power BI model with new data from the Excel source is demonstrated, showing how all visualizations automatically update.
This section explains how to share Power BI reports. While direct sharing of the .pbix file is possible, publishing to the Power BI Service is recommended for security and controlled access. The process of publishing the report to a Power BI workspace is shown, followed by its appearance in powerbi.com. The interactive functionality remains, and the ability to pin live report pages to a dashboard for easy sharing with colleagues is highlighted.
Two powerful features exclusive to the Power BI Service are demonstrated: Quick Insights and Q&A. Quick Insights automatically analyzes the data set to find interesting trends, outliers, and correlations, generating various visualizations. Q&A allows users to ask natural language questions about their data (e.g., 'sales by chain'), and Power BI generates appropriate visualizations on the fly.
The video summarizes the key benefits: Power BI Desktop is free, reports automatically update with new data, and the Power BI Service provides secure sharing and advanced features. It reiterates the three main components of Power BI (Power Query for data cleaning, Power Pivot for modeling, and Power BI for visualization). Finally, it encourages viewers to download the free Power BI Desktop and provided sample files to practice, and promotes the author's online Power BI course for in-depth learning.