My first visual calculation

How does this work?

Now that you’ve seen a few examples of visual calculations and how easy it is to create them, and you’ve caught a glimpse of the possibilities associated with visual calculations, it’s time for us to explain how they work.

Figure 2-32 shows the visual calculations edit mode window for a visual calculation, with all relevant elements highlighted. These include the following:

• Visual preview The visual preview shows you what the visual will look like when you leave the visual calculations edit mode and return to your report. You can see what impact newly added visual calculations have on your visual, and what your visual will look like if you hide certain measures or visual calculations.

• Visual matrix The visual matrix is the data representation of your visual. As mentioned, it shows you the outcomes of all newly added calculations.

• Formula bar You use the formula bar to write and edit visual calculations.

• Panes The visual calculations edit mode window includes the same panes as the report, such as the Visualizations pane (shown here). You use these panes to select other visual calculations that you might want to edit or to format them.

FIGURE 2.32 The visual calculations edit mode window consists of many elements.

The most important concept you need to grasp is that every visual—whether it’s a clustered bar chart, a table, or even a simple card visual—can be viewed as a visual matrix, with the different dimensions of the visual on the visual matrix’s rows and columns, and the calculated values for each combination of dimensions within the visual matrix. Viewing a visual as a visual matrix enables you to add more calculations, which can be seen as new columns in the visual matrix.

It’s important to note that you can only use the values that are already inside a visual matrix for any new calculations you want to add. You cannot create new calculations based on measures or dimensions that are not present in the visual matrix. You can, however, add the necessary measures in the visual and then hide those measures so that they don’t appear in the visual, but they do exist in the visual matrix and can therefore be used in new calculations. It’s even possible to create a visual that displays only visual calculations, as you saw in the year-to-date sales example. In this approach, you can hide intermediate steps in your calculations and show only the final result.

As you have seen, you can perform a calculation in two different ways: by writing a DAX expression that uses measures or visual calculations that are already present in the visual, or by applying expression templates, which provide you with the structure of the calculation, leaving you to fill in the specifics by typing or using the provided parameter pickers.

Power BI includes several new functions created specifically for visual calculations. These include the RUNNINGSUM, COLLAPSEALL, and MOVINGAVERAGE functions discussed in this chapter, but (spoiler alert) there are many more! Part II describes these functions in greater detail.

These new functions include some new parameters, like the axis and reset parameters, which you saw earlier in this chapter. Both of these parameters are discussed in greater detail in Chapter 3. The reset parameter enables you to reset your visual calculation so it starts again from a specific data point. This is very useful if, for example, you are calculating year-to-date values for multiple years in the same visual. As for the axis parameter, it enables you to navigate within the visual matrix in various ways, such as by rows, by columns, or other patterns (discussed further in Chapter 3).