My first visual calculation

Create your first visual calculation

A visual calculation is created inside a visual. For your first visual calculation, you’ll enhance the sample visual that contains Sales and Costs per product category with the total profit per product category. Do so by first selecting the clustered bar chart on the left of the report page. When the visual is selected, there are multiple ways to add a visual calculation to this bar chart:

• Click the New visual calculation button in the Calculations section of the Home ribbon, as shown in Figure 2-2.

  

  FIGURE 2.2 The New visual calculation button in the Calculations section of the Home ribbon.

• Click the More options button (it features three dots) in the top-right corner of the visual, and select the New visual calculation > Custom option, as shown in Figure 2-3.

  

  FIGURE 2.3 Click the New visual calculation option, then the Custom option in the More options button.

• Right-click the visual and select the New visual calculation option in the context menu that opens, as shown in Figure 2-4.

  

  FIGURE 2.4 Right-clicking the visual opens a context menu with a New visual calculation > Custom option.

Performing one of these actions displays the visual calculations edit mode window, which shows your visual as it appeared on the report page at the top and a matrix with data beneath it. The matrix is a representation of the data in the visual and is conveniently called the visual matrix. In this case, the visual matrix contains values for the sales and costs per product category as well as the total sales and costs for all product categories combined. An empty Calculation column appears to the right of these values, as is shown in Figure 2-5. This is where you will create your first visual calculation.

The first question we want to answer is, what is the total profit per product category? The formula for this involves subtracting the costs from the sales. This is exactly what you’ll do to create your first visual calculation.

As explained in Chapter 1, “Introduction to visual calculations,” visual calculations are written in the DAX language. They have a similar syntax as regular measures and should thus begin with a name. We’ll call our first visual calculation Profit.

To create this visual calculation, place your cursor in the formula bar and use ordinary DAX to write the formula. Notice that the IntelliSense feature provides suggestions as you type. In this example, after you type the name for your visual calculation and begin typing the formula, as you press the S key, you will see a list of suggestions that includes [Sales], as shown in Figure 2-6.

When you type a square bracket in the formula bar, you will see all the available columns for use in this visual matrix. In this example, there are three: CategoryName, Sales, and Costs. CategoryName is a column of the Product table. The Sales and Costs columns are the results of the DAX measures Sales and Costs that are defined in the _Measures table. These measures were added to the visual and return values for the context that is evaluated for the visual in which we are creating a visual calculation.

Back to our example. We wanted to create output for the profit, which is defined as the difference between sales and costs. In the formula bar, you can simply type the following DAX formula:

Profit = [Sales] – [Costs]

Press Enter or click the green check mark to finish. This will create a new column in the visual matrix with the name Profit, and in each row, the corresponding value of sales minus costs is evaluated, as shown in Figure 2-7. Congratulations, you have successfully created your first visual calculation!

This new calculation is also added to your visual. As shown in Figure 2-8, the visual should now contain three values: Sales and Costs (which are measures) and Profit (which is your very first visual calculation).

You can add a new visual calculation by writing another DAX statement in the formula bar. You can even use your newly created visual calculation in another visual calculation. For example, you can create a visual calculation to calculate the profit including a 10 percent tax amount by typing the following DAX formula in the formula bar and pressing Enter:

Profit incl tax = 1.1 * [Profit]

This will create a second visual calculation, and thus a new column in your visual matrix and a new bar in the visual preview.

It’s also possible to go back to your report by clicking the Back to report button in the top-left corner of the window, as shown in Figure 2-9.

In the Visualizations pane of the visual that you just edited, there are two new values on the X-axis, called Profit and Profit incl tax (assuming you created both visual calculations described so far), as shown in Figure 2-10. To help you differentiate between measures, columns and visual calculations, the visual calculations are identified by a small icon that shows a column chart with fx in front of it.

There are also different options in the dropdown on the right side of the data fields. For regular measures and columns, there are options for Show value as and New quick measure. But for visual calculations, there is an Edit calculation option instead. Clicking this option returns you to the visual calculations edit mode window, with the visual preview on top and the visual matrix, where you can edit your visual calculation, below it.

Create a year-to-date sales calculation

The report page contains a visual that shows the sales per month, but it’s very common to look at sales on a year-to-date basis, instead—hence, our second question earlier in this chapter: what are the total year-to-date sales? Let’s see if we can create another visual calculation for year-to-date sales. In this example, you will add a new year-to-date sales calculation to the clustered column chart that contains the sales per month, shown in Figure 2-11.

As before, begin by selecting the visual you want to work on—in this case, the column chart. Then add a visual calculation to open the visual calculations edit mode window, with the column chart on top and the visual matrix below it. This time, the visual matrix has one column for the month, which lists all the month names, and one column called Sales, where the values for the data in the column chart are listed. At the bottom, there’s a total row that shows the total sales over all the months.

To calculate year-to-date sales, you add up the sales for all previous months, plus the sales for the current month. So, for January, the year-to-date sales would be the same as the regular sales because it’s the first month of the year. But for February, the year-to-date sales would be the sum of the January sales and the February sales. And for March, it would be the sum of January, February, and March.

Because users frequently need to obtain running sums, like year-to-date sales, visual calculations offer an expression template for this operation. An expression template is a predefined template to compute certain calculations in visual calculations. To view and insert available expression templates (of which there are several), click the fx button for the visual calculation to the left of the formula bar (see Figure 2-12). These expression templates can be selected in the visual calculations edit mode, as well as in the ribbon (refer to Figure 2-2), the More options button (refer to Figure 2-3), and the context menu (refer to Figure 2-4).

For this example, select the first expression template in the list, Running sum. As shown in Figure 2-13, expression templates provide easy-to-use parameter pickers that enable you to quickly select parameter values without having to type them. In addition, this expression template automatically names your visual calculation Running sum and adds the RUNNINGSUM DAX function. For this example, let’s change the name of the visual calculation to YTD Sales instead of Running sum because that is a better description of what you want to calculate.

As shown in Figure 2-13, the DAX function RUNNINGSUM asks for at least one parameter, as indicated by the [Field] parameter picker. However, our data does not contain a Field column, as indicated by the red squiggle. You need to replace this field with the column for which you want to calculate the running sum. To do that, either click the parameter picker and pick [Sales] as shown in Figure 2-14 or delete the [Field] placeholder in the formula bar and type a square opening bracket to see a list of all the available options for the current visual (in this case, Month and Sales).

To calculate the running sum of the sales data, select [Sales] to generate the following DAX statement:

YTD Sales = RUNNINGSUM ( [Sales] )

Then press Enter or click the green check mark to the left of the formula bar to create the new visual calculation in your visual. The resulting visual calculation should appear in the visual preview in the top half of the screen (see Figure 2-15).

The values are exactly as you would expect, which is best seen in the visual matrix at the bottom of the screen (see Figure 2-16).

As expected, the two values for January are the same, because the YTD sales in January consist only of sales during that single month. The other values are also as expected, with YTD sales in February equaling the sum of the January and February sales, and YTD sales in March equaling the sum of the YTD sales in February and the March sales. And, as expected, at the end of the year, the YTD sales in December equal the grand total of all the individual sales per month, resulting in a total of approximately 449 million—which is also the total for the YTD Sales column.

Suppose you want to view the YTD sales per month, but not the sales for each individual month. You cannot simply remove the Sales measure from the visual because you need that data to calculate the YTD sales. You can, however, hide that data. To do so, make sure you’re in the visual calculations edit mode, locate the Sales measure in the Visualizations pane, and click the eye icon. (This is the same icon you use to hide or show columns or tables in the table view of a Power BI semantic model.) A diagonal line will appear through the icon, indicating that it’s now hidden (see Figure 2-17).

Notice that the visual matrix at the bottom of the visual calculations edit mode window is not affected by hiding the Sales measure. It still contains two columns, Sales and YTD Sales, and rows for each Month. However, the visual preview in the top part of the screen contains only the YTD Sales for each Month, as does the visual on the report page.

Notice, too, that the Y-axis setting in the Visualizations pane for the visual on the report page lacks an entry for the Sales measure. However, if you hover your mouse pointer over the information icon above the Y-axis setting (see Figure 2-18), Power BI informs you that there are more fields in the visual, but they are hidden. You can still access and use them when you open the visual calculations edit mode window.

Set a format

When you create a measure, you often need to format it, so it looks how you want it to in your report. But how does this work for visual calculations? To find out, let’s use the example you just created for year-to-date sales.

The YTD Sales visual calculation doesn’t have any format set; neither does the Sales measure from which it is calculated. So, let’s start with that. If you change the Sales measure to a currency format with a dollar sign in front of it, you won’t see any changes in the visual. This makes sense because even though the Sales measure is in that visual, it’s hidden from view. If you switch to the visual calculations edit mode, though, you do see the change in the visual matrix, with all the values in the Sales column preceded by a dollar sign (see Figure 2-19). However, the YTD Sales column has not changed. This is because visual calculations do not take on the format of any measures used to create them—although it does try to guess, based on the function you used and your input data types. In this case, the result is not what we want. We want to format it with a dollar sign in front, just like the Sales measure.

Fortunately, it’s possible to set a format for a visual calculation. You do this in the visual’s Visualizations pane. Simply click the General tab, open Data format, and choose YTD Sales from the Apply settings to menu. Then open the Format menu under Format options and choose Currency. Make sure that the Currency format menu is set to $ English (United States) as shown in Figure 2-20.

This change is also visible in the visual matrix, with entries in the YTD Sales column formatted as a currency with the dollar sign in front. Furthermore, because the visual matrix is just a matrix representation of the data in the visual, the change is also visible in the visual preview, with the axis now containing a dollar sign. The values in the tooltip are formatted as currencies as well.

Calculate the profit per country

The third question posed at the beginning of this chapter was, how is the total profit divided over the different countries?

Earlier you created a visual calculation to calculate the profit, but that visual calculation is not available in the Data pane of our model for use in a new visual. However, it’s available in a visual that you have already created—the one that shows Sales, Costs, Profit, and Profit incl tax by CategoryName. So, you can copy that visual to a new report page and change it as needed to answer our question.

To start, copy the aforementioned visual and paste it on a new report page. Then remove the Profit incl tax entry from the Visualizations pane because you don’t need that data. Finally, change the Y-axis data field from CategoryName to the CountryName from the Store table. The result will be a clustered bar chart that shows Sales, Costs, and Profit by CountryName, as shown in Figure 2-21.

This visual currently contains Sales and Costs measures, which you don’t need to show there. However, you do need these measures to calculate the profit. So, rather than removing the Sales and Costs measures from the visual, you need to hide them. To do so, open the visual calculations edit mode window and click the eye icon next to the Sales and Costs entries (see Figure 2-22).

You now have a visual that shows the profit for each country. But the visual does not show the percentage of the total profit for each country, which is what you’re really after. One way to obtain this is to simply change the visual from a clustered bar chart to a pie chart. This will display the profit per country as part of the total profit, and it displays the percentages as well. However, because there is a known aversion to pie charts among the Power BI community (including some of the authors of this book), we will take a different approach: creating a visual calculation that shows these percentages in a bar chart.

To calculate the profit of each country as a percentage of the total profit, you need to divide the profit for each country by the total profit (which is already visible in the Total row of the Profit column). To achieve this, you can create a column that displays the total profit for all countries on each country row, and then divide each country’s profit by this total profit to obtain a percentage.

If you were to do this using DAX measures, you would need to change the context to remove the filter that is set to a specific country for each row. So, you could use CALCULATE and a function like REMOVEFILTERS or ALL to obtain the total profit on the rows for each country. But in the spirit of making DAX easier, we’ll use a pair of functions created specifically for visual calculations: COLLAPSE and COLLAPSEALL. These two functions make it possible to pick a field in the visual matrix and collapse the lattice, returning the collapsed value of the column. We will discuss how this works in Chapter 3 and discuss the functions in detail in Part II.

To start, create a visual calculation with the following DAX statement:

Total Profit = COLLAPSEALL ( [Profit], ROWS )

This creates another column in the visual matrix with the total profit displayed on each individual row, as well as the Total row (see Figure 2-23).

The next step is to create yet another visual calculation to calculate the percentage of the profit per country as a part of the total profit. You can use the following DAX to obtain a new column that contains these values:

% Profit = DIVIDE ( [Profit], [Total Profit] )

Then use the General Format settings in the Visualizations pane to select the Percentage format for the visual calculation (see Figure 2-24). Figure 2-25 shows the resulting visual matrix.

You can now hide both the Profit and the Total Profit visual calculations to obtain a bar chart with just the percentages of the profit per country, as was our goal (see Figure 2-26).

Now, this approach works just fine. But you can obtain the percentage of the total profit per country more easily by using an expression template—specifically the Percent of grand total expression template. This expression template creates a visual calculation that does exactly what we did here, but all in one step. It uses the following syntax:

Percent of grand total = DIVIDE ( [Field], COLLAPSEALL ( [Field], Axis ) )

Simply replace the [Field] parameter picker with [Profit] (because you want to divide the profit by the total profit) and the Axis parameter picker with the keyword ROWS (to indicate that you want to collapse the rows to obtain the total profit) and press Enter. Change the format to a percentage, and you’ll get the result as shown in Figure 2-27. To indicate that this calculation uses the Profit column, the header of the Profit column is lined with a dotted line. This uses the highlight functionality of visual calculations, as described in more detail in Chapter 4. Notice that the values of the Percent of grand total visual calculation are identical to those of the % Profit visual calculation.

Reset your calculation

Now that you have seen some simple examples, it’s time to explore additional functionality within visual calculations. If you recall the calculation you created for year-to-date sales per month, you might remember that there’s a slicer on the report page to select only one year of data. This is on purpose; a year-to-date calculation that sums up values over different years might not give as much insight to a business user. So, you’ll probably want to start a new calculation each year. In other words, you want to reset the calculation for each year. Fortunately, resetting a calculation is quite simple and involves a function you’ve already used: RUNNINGSUM.

When employing RUNNINGSUM to calculate the year-to-date sales, you used the following visual calculation:

YTD Sales = RUNNINGSUM ( [Sales] )

This statement has only one parameter, which is the field you want to sum over. However, the function RUNNINGSUM supports the use of additional parameters to enable you to create more complex calculations—specifically the axis, orderby, blanks, and reset parameters—as shown in the following function signature:

RUNNINGSUM ( Expression, [Axis], [OrderBy], [Blanks], [Reset] )

Only one of these parameters—the first one—is mandatory. The other four are optional. In this example, you want to apply the fifth parameter (reset) to reset your calculation. When you apply this parameter, the function resets the running sum whenever the parameter’s value changes.

In this example, you want to reset the running sum each year. The effect of the reset parameter is best seen if you create a new matrix in the report with the Year and Month columns from the Date table on the rows and the Sales measure as the expression, or value. The expression for this is as follows:

YTD Sales = RUNNINGSUM ( [Sales], [Year] )

This expression is very similar to the one you used earlier, but in this case, you have added a reset parameter ([Year]) to indicate that you want to reset the calculation when the Year value changes on January 1 of the next year.

Figure 2-28 shows the resulting visual matrix. Notice that the values for the first year are the same as in the example where we did not use the reset parameter. That is, for each month, the previous months are summed up to calculate the year-to-date sales, and the totals for 2024 are the same for both the sales and the year-to-date sales. The values are identical again for January 2025 because the calculation for the year-to-date sales has been reset for the new year. Finally, the value of the year-to-date sales for February 2025 is the sum of January 2025 and February 2025, and so on. Now the calculation does exactly what you want it to do, even if you show multiple years in your visual.

Create calculations that navigate over the columns

So far, all of our examples have involved calculating over rows within columns in the visual matrix—summing up different values within one column in the running sum or collapsing all the values in a column to a total value. But what if you want to calculate over columns instead of rows?

Enter the axis parameter. You’ve already seen this parameter twice in this chapter. It was a mandatory parameter for the COLLAPSEALL function and an optional one for the RUNNINGSUM function. To see how this parameter works, you first need to create a visual with a field dimension on the columns in the visual matrix. In the example semantic model, you can create a report page with a matrix that contains CategoryName from the Product table on the rows, YearQuarter from the Date table on the columns, and the familiar Sales measure. Figure 2-29 shows the resulting matrix visual and part of the Visualizations pane.

If you add a visual calculation to this visual, you’ll notice that the visual matrix now has a different format than in all the previous examples. You’ll still see the CategoryName on the rows, but because the YearQuarter is on the columns, you’ll get a new column for each value of YearQuarter. If you first create a new visual calculation, you’ll see two columns for each YearQuarter: Sales (with the value of the Sales measure) and Calculation (which is empty), as shown in Figure 2-30. This is where you’ll create your new visual calculation.

For the sake of example, let’s create a calculation that returns the moving average for multiple quarters by applying the MOVINGAVERAGE function and specifying an axis. The signature of this function is as follows:

MOVINGAVERAGE ( <Column>, <WindowSize>[, <IncludeCurrent>][, <Axis>][, <OrderBy>]
[, <Blanks>][, Reset] )

The first two parameters are mandatory. However, all the others are optional, as indicated by the square brackets. In this example, it makes sense to calculate a moving average over the last four values so that for each quarter you calculate the average for a whole year (all four previous quarters). This will help business users look at their data without a seasonality pattern. For example, if you sell ice cream, it’s expected that you’ll sell much more during the months when the temperature is higher. To see if your sales have increased since the previous year, you can compare this year’s monthly value with the same month of the previous year, but that only gives you insight about that one month. If you calculate the average sales over the last 12 months, however, you can see per month whether your sales have increased on a yearly basis.

In our example, to calculate the moving average over the last four quarters, you can use the following visual calculation:

MA = MOVINGAVERAGE ( [Sales], 4, TRUE, COLUMNS )

The windowSize parameter is set to 4, indicating that you would like to average over the last four values. The includeCurrent parameter is set to TRUE because you want to include the current value when you calculate the moving average for the four values that you are averaging. (You could skip this, because it’s an optional parameter and its default value is TRUE, but we’ve included it here for clarity.) Finally, the axis parameter is set to COLUMNS, indicating that you want to calculate this moving average over the columns. Figure 2-31 shows the result.

To confirm that the values are as you would expect, look at the row for the Audio category. You’ll see that the moving average value of Q1-2024 is the same as the sales value. This is because there are not yet any additional values to include in the moving average. The moving average for Q2-2024, however, is different from the sales value, because it now averages two values (Q1-2024 sales and Q2-2024 sales). The same is true of the moving average for Q3-2024 (which averages sales values of the first three quarters of the year) and Q4-2024 (which averages sales values of the first four quarters). The first quarter of 2025 is the interesting one. Here, you would expect the moving average to average sales values of Q2-2024 ($1.35 million), Q3-2024 ($1.34 million), Q4-2024 ($3.08 million), and Q1-2025 ($5.78 million). The average of these numbers is approximately $2.89 million, which equals the value returned by the moving average calculation for Q1-2025. So, it works exactly as anticipated.