DAX Visual Calculations: Bringing Excel Simplicity to Power BI

If you’ve worked with DAX (Data Analysis Expressions) measures in Power BI, there’s a good chance you have a love-hate relationship with them. On one hand, DAX is easy to start learning and incredibly powerful. On the other hand, there’s no way to directly refer to data inside of a visual, making calculations like running sums and moving averages difficult to build. Thankfully, that’s about to change as Microsoft rolls out visual calculations for Power BI which will allow you to build DAX calculations that are defined inside of a specific visual, similar to how calculations are done in Excel.

Benefits of Visual Calculations

Visual calculations are built inside of a report’s visuals, so their code is executed directly on the visual they’re built in. Since this code is specific to a single visual and directly references the data in that visual, it’s easier to write the DAX and easier to maintain. On top of this, visual calculations operate on the aggregated data already found inside the visual, which usually results in better performance than a standard DAX measure that has to operate on the detail level of the source data. 

Building Visual Calculations

Many existing DAX functions can be used in visual calculations and will work similarly to calculated columns. Templates are also available to make it easier to write common visual calculations such as running sum, moving average, and percent of grand total. Additional details can be found in the example below.

As of today, visual calculations are still in preview mode, so you’ll need to first enable them by going to File > Options & Settings > Preview features and checking the box next to Visual Calculations. After you’ve done this, creating a new visual calculation is as easy as selecting a visual and pressing the New Calculation button on the “Home” tab.

In this example, we’re going to quickly build a running sum calculation that resets at the beginning of each year. We’ll be performing this calculation on the Sum of Units Sold data in Microsoft’s financials test dataset, found by going to File > New > Report > Use sample data > Load sample data. Below are the steps and the  simple DAX needed to accomplish this:

1. Select your visual. The visual in this example is a Matrix showing the Sum of Units Sold with a Year/Quarter/Month date hierarchy on the row axis.
   
2. Press the New Calculation button on the “Home” tab.
   
3. The visual calculation window will open. This window contains a visual preview that shows your visual, a formula bar where you can build your calculations, and a visual matrix that displays the results of the visual calculations as you create them.
4. In the formula bar, give your calculation a name and create the DAX code. Here you can see that the calculation is as simple as calling the “RUNNINGSUM” function and referring to the Sum of Units Sold as the column to execute the running sum on. The  “HIGHESTPARENT” property at the end of the calculation resets the calculation at the beginning of each year because, for this visualization, the highest parent on the row axis is the year. Note that you could also press the fx button next to the formula bar and choose “Running sum” from the list of common visual calculation templates rather than manually typing the calculation.
   
   In this example, I’ve named my calculation “Running Total by Year.” In a normal DAX measure, this name would not be recommended because it’s ambiguous (running total of what?). However, because visual calculations are used only in the visual they’ve been defined on and the Sum of Units Sold column is the only other calculation in this visual, this name is fine.
5. Once you’ve created your calculation, click Back to Report at the top of the page and you’ll see your updated visual with the new visual calculation. Notice how the calculation resets in January 2014.
   

When to Utilize Visual Calculations

Visual calculations are a fantastic option when you need to build an Excel-like calculation that specifically references a column, measure, or another visual calculation in a visual. They’re usually easier to create than standard DAX calculations, and allow you to focus on the results rather than spending time figuring out complex filter contexts and all the ways your new calculation might need to interact with your data model.

Visual calculations aren’t perfect though. As of this writing, there are a handful of limitations, such as:

• Underlying data that can’t be exported from visuals using visual calculations.
• Filters that cannot be applied to the results of visual calculations.
• Some visuals, like tree maps, geographic maps, and small multiples, are unsupported.

There are also times when using a visual calculation will work, but you might be causing yourself more effort in the future by using them. For example, if you have 10 different visuals that all need a running sum of units sold by year, it will likely take you longer to build your running sum visual calculations for all 10 visuals than it would be to build a single standard DAX measure and apply that to each visual. Also, if you ever need to change the calculation in the future, it would probably take you longer to change the 10 visual calculations than it would to change the single standard DAX measure. Generally, if visual calculations are supported for your use case, and the calculation you’re building isn’t going to be repeated many times throughout your report, it’s worth your time to explore the visual calculation option.

What Will You Use Visual Calculations For?

Unless you’re already a DAX wizard, we think you’ll find visual calculations to be a powerful and time-saving tool when building Power BI reports. But don’t take our word for it…give them a try in your next report and see for yourself! For additional information on visual calculations, see Microsoft’s visual calculations article.

Looking for more? Visit our blog to find more best practices, insights, and updates from our experts.

| FEATURED AUTHOR: NATHAN WAACK, SENIOR DATA CONSULTANT