The first chart in Figure 7-2 compares Sales Amount and the Target values using a simple line chart.
Figure 7-3 shows this in more detail.
127 CHAPTER 7 | Improving Power BI reports Figure 7-3: A line chart of sales amount by date.
The line chart is the primary choice when you display a measure over a date range or time, using the x-axis for the temporal dimension. You can select the colors of the different measures by using the Data Colors properties, as demonstrated in Figure 7-4. Here you can select the color of each measure included in the line chart.
Figure 7-4: The Data Colors properties for a line chart visualization.
You can use a slight variation of the line chart when a measure that you want to display is part of another measure. For example, consider the Sales Amount and Sales Cost measures. Hopefully, Sales Cost is always lower than Sales Amount, and the graphical distance between them represents this margin. With the line chart, the gap between the two measures might not be clear, so you might want to “paint” the area below the line by using the values of the measures along time. The area chart does exactly this, as illustrated in Figure 7-5.
Figure 7-5: An area chart of sales amount versus sales cost by date.
128 CHAPTER 7 | Improving Power BI reports
The y-axis must begin at zero; otherwise, the area would not be fully representative of the two values. The visible gray area expresses the delta between the two measures in a graphical way and corresponds to the margin. You should not use an area chart when you have several intersections between different lines. You should consider it only when measures do not intersect often. The example in Figure 7-5 demonstrates one of the few cases for which you can consider using it.
Note For the sample reports in this chapter, for the most part we do not use pie charts and donut charts. This is because they are not considered a best practice, with an exception that you will see in the last section. The human brain can more easily make comparisons between lengths (as in a bar chart) than between angles (as in pie and donut charts).
Comparing measures with different scales requires particular visualizations. You need to display two y-axes, and you need a way to easily associate the axis corresponding to each measure. For example, Figure 7-6 shows a line and stacked column chart that yields more details by displaying the sales amount measure divided by category and class, compared with the margin percent divided by category.
Figure 7-6: A line and stacked column chart of sales amount and margin percent by category and class.
The scale of the main measure (sales amount) is represented on the left y-axis, and the other measure (margin percent) is on the right y-axis. The x-axis shows the name of the category corresponding to each column, which is also divided by class using different shades of gray. Figure 7-7 shows the properties of this component used to bind data. The x-axis is called the shared axis, and it can include more than one attribute. In this case, we used two product attributes: Category and Subcategory. This makes it possible to perform an interactive drill-down of data in Power BI.
129 CHAPTER 7 | Improving Power BI reports Figure 7-7: Data binding properties for a line and stacked column chart.
You can activate the drill-down feature for the selected column chart by clicking the drill down button (the down-arrow icon) located in the upper-right corner of the visualization. When drill-down mode is turned on, the drill-down button changes to display a black background, as depicted in Figure 7-8.
Figure 7-8: The drill-down button in a visualization. The black background signifies that drill-down mode is turned on.
With drill-down mode activated, when you click a column within the chart, you can drill down to that column’s respective subcategories. Figure 7-9 shows the resulting chart when you click the Computers column in Figure 7-6. Note all of the subcategories that are related to the Computers category.
Figure 7-9: A line and stacked column chart of sales amount and margin percent by subcategory and class for the Computers category.
To drill up, in the upper-left corner of the visualization, click the drill-up button (the up-arrow icon), as highlighted in Figure 7-10.
Figure 7-10: The drill-up button in a visualization.
After you move up to the product category again, you can drill down to all of the subcategories for every category by selecting the double-arrow button in the upper-left corner of the visualization, as illustrated in Figure 7-11.
130 CHAPTER 7 | Improving Power BI reports Figure 7-11: The drill-down button in a visualization.
When you drill down to the subcategory level for all the categories, you obtain a chart similar to that shown in Figure 7-12. Both the Sales Amount and Margin percentage measures have the
subcategories granularity in the chart, so you can still compare them.
Figure 7-12: A line and stacked column chart of sales amount and margin percent by subcategory and class, for all the categories.
More info You can find an animated guide on how to use the drill-down feature in Power BI at https://powerbi.microsoft.com/documentation/powerbi-service-drill-down-in-a-visualization/.