Visual Calculations in Power BI are a feature (released as Public Preview Feb 2024) that allows users to apply calculations directly to visuals without creating separate measures. This includes common functions like running totals, moving averages, and percentage of total, making it easier to add dynamic insights to reports quickly. The calculations are applied at the visual level, streamlining the report-building process and keeping the data model cleaner. This feature is particularly useful for creating ad-hoc reports or testing calculations without needing to modify your underlying data model.

To enable Visual Calculations, you must do nothing! They are enabled by default. If for any reason you do not see that visual calculations are enabled, you should 1. ensure that Power BI Desktop is updated 2. If you would like to see where preview features “live” in Power BI (or if you would ever like to turn a Preview off) you’ll want to navigate to Options > Preview Features, and restart Power BI Desktop (if you changed anything). Because Visual Calculations are enabled by default, the functionality becomes accessible directly from the visual settings, allowing for rapid and intuitive calculation adjustments that save time and enhance reporting capabilities.

Why might you use Visual Calculations?

As stated above, visual calculations are created on the visual, independent of the data model. In my average use case, I use Visual Calculations to create additional columns on tabular visuals. However, this doesn’t come without some considerations with how the end user will interact with their data.

Visual Calculations Use Case, Considerations, and Limitations

Creating a Profit column and a Profit Percent column using Visual Calculations in Power BI is a great example of how this feature can simplify report design. This is a great example of a use case that I use exponentially. By adding a Profit calculation (e.g., Sales – Costs) directly to a visual, you can focus on specific scenarios without cluttering the data model with permanent calculated columns. Similarly, a Profit Percent column (Profit ÷ Sales) provides immediate context for profitability analysis in the visual without extra setup in the model.

Benefits of Using Visual Calculations

• Ease of Use: Visual calculations are applied at the visual level, allowing you to see results immediately without altering the underlying data model.
• Flexibility: These calculations are perfect for quick, ad-hoc insights or when testing a new approach.
• Clean Data Model: By not adding measures or calculated columns to your model, you keep it less cluttered and easier to maintain.

Restrictions with Visual Calculations

However, visual calculations have limitations, particularly with formatting and advanced interactions:

• Formatting Restrictions: Visual calculations often have limited options for custom formatting. For example, you might not be able to format the Profit Percent column as a percentage unless the visual inherently supports it
• Exporting Data: Data from visual calculations cannot be exported, which may limit the usability of reports for some stakeholders.
• Reuse: Unlike calculated columns, visual calculations cannot be reused across multiple visuals, which might require duplicating logic.

Why Choose a Calculated Column Instead?

Using a calculated column might be preferable in scenarios where:

1. Consistency Across Reports: If Profit or Profit Percent is a commonly used metric, creating a calculated column ensures it is consistent across all visuals and reports.
2. Advanced Formatting: Calculated columns allow for specific formatting options that visual calculations might not support.
3. Export Requirements: Data in calculated columns is part of the data model, making it exportable and easier to analyze outside Power BI.

The Secret Sauce (Tips & Tricks)

Because Visual Calculations are still in Public Preview, they are what I like to call in their infancy. It is a feature that is sure to evolve over time. While playing with Visual Calculations there are some nuances, and tips that I have picked up over time.

1. Formatting: Above I explained that formatting is a limitation of visual calculations. However, just because formatting options can be limited does not mean that they are nonexistent. In the case of adding a Visual Calculation to a table, there are a few ways you can format the column. To do so, ensure you are clicked on the visual > Format > Visual > Specific Column > Select your Visual Calculation > Apply to Values. Here you can change the way that the data is displayed and color. As of August 2024 visual level format strings were initiated enabling you to add formatting options to the visual calculation. For our example of a percentage, you can go to Format > General > Data Format > Format for Percentage would be #.#%
2. Use Templates: One of the main functions of Visual Calculations is make DAX more accessible. Out of the box Visual Calculations provide templates that make functions that were previously challenging to create in DAX a breeze. An example of this would be to create a running sum. To access templates, create a new Visual Calculation and hit the drop down next to FX. Once you select your template the skeleton of a DAX formula will be input into your formula bar and from there you can add your appropriate fields. This enables a great starting point.

Like I stated above Visual Calculations are still evolving. I suspect that over time they will become a powerful asset with more natively include features and functionality.

Stay up to date with what is happening in Power BI by checking out the Microsoft Power BI Blog

Also stay up to date with Jeroen ter Heerdt (Microsoft PBI PM) on LinkedIn who frequently posts Microsoft Updates & is a Visual Calculations Fan Boy