What we will cover
In the last section we looked at the fundamentals of visualization in Power BI. Now we will see how we can use our visuals to analyse the data. We will cover:
- How to enhance reports to expose insights
- Perform advanced analytics
How to enhance reports to expose insights
We can add customization to our visuals using conditional formatting. We will create a bar chart with conditional formatting using a measure that returns a green color if sales are above $30M and red if sales are below. Hex codes are used in the measure to describe the color we want. Note, a useful site to get hex colors that designers use can be found at https://colorhunt.co/.
In our measures table, right click and create a new measure called ‘Sales CY Conditional Format.’
Sales CY Conditional Format = IF( sum(sales[Sales Amount]) >= 30000000,"#1eae98", "#f55c47" )
Next we will create a stacked column chart. Add Calendar Year to the axis well and the SUM of Sales Amount to the values well.
On the format section on the right, select Data colors and then the ‘fx’ symbol for conditional formatting.
On the ‘Format by’, select Field value. Below in the ‘Based on field’ select our measure Sales CY Conditional Format. You will now have a conditionally colored chart where values above $30M will be green and below will be red.
While we are on the Sales Amount by Year chart, there is one interesting functionality I want to show called ‘Quick Insight.’ Often in visuals, you see a large change values, and wonder the cause of such fluctuations. To see how this works, right click on the bar chart on 2019, go to analyse and click on explain the increase.
Power BI will now create a chart to explain the 41% increase in sales from 2018 to 2019. The resulting chart is a waterfall by subcategory, where we can easily identify Touring bikes as the biggest driver.
Another common analytics skill is to filter the Top N of a data series. For instance, let’s show the top 5 subcategories by sales in a table. Open the Filters section on the right by clicking the arrow if it is minimized. Next, select ‘Top N’ and then select 5. In the value well add in Sales Amount. You now have a filtered table for the top 5 sales by subcategory!
Histograms are a common way to display the distribution of your datasets. For instance, say you wanted to know the distribution of orders in buckets of 5 e.g. <5 qty ordered, between 5 and 10 qty ordered etc. The first thing to do is create the groups for your data. Head to the Order Quantity field and click the 3 dots on the right. Then select ‘New group’.
In the groups section we can define the Bin size or we can specify the number of bins we want. Select ‘Size of bins’ and make the bin size 5.
Select the stacked column chart and under the Axis add Order Quantity (Buckets), the grouped field we just created. In the values, add the SUM of Order Quantity. We now have a histogram of the the order size that shows us a positively skewed or right tailed distribution.