Advanced Analysis with Power BI
The course is part of this learning path
Advanced Analysis with Power BI examines various methods for teasing out insights from data using statistical methodologies and presenting significant findings in visually compelling formats. The course starts with basic statistics such as standard deviation and then progresses to AI and machine learning analysis where Power BI does all the heavy lighting allowing the user to investigate and dynamically explore significant findings.
- How to use Z-scores to display outliers and use the Outlier Detection visualization from Microsoft
- How to use Power BI's Anomaly Detection and Fluctuation Analysis functionality
- Use time-series forecasting to predict future data points with varying degrees of certainty
- Use groups to classify categorical data and bins to categorize continuous data
- Learn about Key Influencers
- Use the Decomposition tree to drill down into a metric manually using known factors or let AI functionality determine which factors are the major contributors
- Use the power of Azure's AI and machine learning to analyze text for positive and negative sentiment, keywords and phrases, and image tagging
This course is intended for anyone who wants to discover insights hidden in their data.
- Have a basic understanding of statistics, like knowing the difference between a mean and median, a normal distribution, and conceptually how standard deviation is related to that
- Know how to connect a data source, load data, and generally use the Power BI Desktop and Power Query Editor environments
- AI Insights demonstration requires a PowerBi.com premium account
The decomposition tree visualization is a great way to graphically drill down and explore your data with the assistance of AI. Here I have a dataset of cellular phone and accessory sales that I've restricted to 2009 and ten with a slicer control. I'll drop a decomposition tree on the report page and use the LineTotal column, which is analogous to the amount paid for a line item in a sales transaction, onto the analyze field. The explain by fields are essentially columns that you used to drill down into the data or have the decomposition tree used in conjunction with AI root cause analysis.
As this is sales data of quite a limited nature, I'll use the product hierarchy columns along with the sales rep and sales month as explain by fields. The starting point is the analysis field with a small + sign where you select how you want to split your data down to the next level. When you click on the plus sign, with AI splits enabled, which is the default, your choices are either high or low AI determined values plus all the explain by fields that haven't already been displayed. I'll drill down through the product hierarchy initially, not using AI splits. After class comes the product itself, so I'll add that description into my explain by fields. Each tree level gets a heading showing which explain by field is in effect with the selected value as a subheading. You can lock the explain by field for a level in place with the padlock icon to the left of the level heading.
Now, if I get rid of all those levels and start again, but this time use the high-value, where the AI analysis menu items are denoted with the lightbulb, I still get the same answer. This is saying that the mobile department has the highest value of all the explain by fields, no matter which tree level or position in the explain by list it occupies. In the format pane, we see that absolute is the default split type. This is absolutely correct, excuse the pun, as mobile is the biggest selling category of a cellular phone retailer.
Let's see what happens when I change the analysis type to relative. Department is replaced by description, which is the individual item's description. There are just over a dozen departments, but there are thousands of items. Relative looks at the metric being analyzed and divides it by the number of distinct items in a column or category. Then, it works out where the biggest difference between the largest (or smallest in the case of low value), and average values is. Apart from the usual color formatting options for elements of the graphic, you can specify the tree display density.
Whether absolute or relative, AI analysis selects from the remaining explain by fields regardless of their order. You can mix and match analysis types at different branch levels within the tree. You can also choose specific fields and then AI split. As we saw before, individual product items come after class in the hierarchy, but if I select absolute high-value, then out of sales rep, month, and product description, we get November, month 11.
A greater LineTotal value is present in November than any sales rep or product description value. Months in the product hierarchy are inconsistent, but to see the decomposition tree split over another dimension, like months, we can add another chart to the report. I'll drop a bar chart on the left using line total and month name as my axes. Now when I drill down through the branch levels of the tree, the month graphic displays the selected level as a proportion of the parent level split over months.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.