How to Do Visual Calculations in Power BI

Adding calculations on the fly directly within your Power BI visuals is a game-changer for quick analysis. This new visual calculations feature lets you create formulas that only exist on a specific chart, much like you would in a spreadsheet column. This article walks you through how to use visual calculations, from enabling the feature to creating common calculations like running totals and moving averages.

What Are Visual Calculations in Power BI?

Visual calculations are DAX calculations that you define and execute directly on a visual, instead of creating them in your data model as a measure or calculated column. Think of it this way: when you create a standard DAX measure, it exists globally in your Fields pane and can be used on any visual. A visual calculation, however, is scoped entirely to the single visual you create it in.

This approach closely mirrors the experience of working in Excel, where you might add a new column to a table to see a quick calculation like "Profit Per Unit" or a "Year-over-Year Growth" percentage. You're not adding that formula to your master dataset, you're just adding it to that specific view for quick analysis.

Why use them?

• Simplicity: They dramatically simplify formulas that used to require complex DAX, like running totals or period-over-period comparisons. Functions like RUNNINGSUM and MOVINGAVERAGE are now built-in.
• Context-Awareness: They operate on the data currently aggregated in the visual. This means you don't have to worry as much about complex filter contexts or CALCULATE functions.
• Cleanliness: You avoid cluttering your Fields pane with one-off measures that are only relevant to a single chart. This keeps your data model tidy and easier for others to navigate.

Enabling Visual Calculations (A Quick Prerequisite)

As of this writing, visual calculations are a preview feature. This means you have to manually enable it before you can use it. The good news is that it only takes a few seconds.

1. In Power BI Desktop, navigate to File > Options and settings > Options.
2. In the menu that appears, click on Preview features.
3. Find and check the box next to Visual calculations.
4. Click OK and restart Power BI Desktop for the change to take effect.

Once you've done this, the feature will be available for you to use in any of your Power BI reports.

Creating Your First Visual Calculation: A Running Total

The best way to understand visual calculations is to build one. A running total (or cumulative sum) is a classic example that showcases how simple this feature makes formerly complex tasks.

Let's say we have a simple line chart or table showing total sales by month.

Here’s how to add a running total visual calculation:

Step 1: Select Your Visual and Enter Calculations Mode

First, click on the visual you want to modify. In our example, it's a table with 'Month' and 'Total Sales'. Once selected, a new option will appear in the Home ribbon called New calculation. Click it.

This will bring you into the visual "Calculations mode." The view changes to show a formula bar at the bottom, a simplified version of your data pane showing only the fields present in the visual, and your actual visual editor.

Step 2: Write the Visual Calculation Formula

With the editor open, you can now write your DAX formula in the formula bar. For a running total, we'll use the new RUNNINGSUM function. It's incredibly straightforward.

The formula takes the field you want to sum as its argument. In our 'Total Sales' column, the underlying measure is [Total Sales]. So, the formula is:

Running Total = RUNNINGSUM([Total Sales])

As you type, you'll notice Power BI's IntelliSense suggests fields and functions, which is really helpful. Once you've entered the formula, press Enter or click the checkmark icon.

Step 3: See the Result and Exit Calculations Mode

Instantly, a new column called "Running Total" will appear in your visual, calculating the cumulative sales month by month. You've just created a calculation that previously required much trickier DAX like CALCULATE combined with filter manipulations.

To go back to the standard report view, simply click the Back to report button at the top of the canvas.

That's it! Your table now has a column that is calculated entirely at the visual level.

Exploring Other Useful Visual Calculation Functions

Microsoft introduced several powerful new functions specifically for visual calculations that make common analyses incredibly intuitive. Let's look at a few practical examples.

1. Moving Average: Smoothing Out Volatility

A moving average is perfect for seeing the trend in noisy time-series data, like daily website traffic or weekly sales. It smooths out the peaks and valleys by averaging the current data point with a specified number of previous data points.

• Function: MOVINGAVERAGE(field, window_size)
• Example: Let's calculate a 3-month moving average of our sales data on a line chart.

Select your line chart, enter calculations mode, and use this formula:

3-Month Moving Average = MOVINGAVERAGE([Total Sales], 3)

This will add a new, smoother line to your chart representing the trend over time, making it much easier to interpret performance without getting distracted by a single good or bad month.

2. Previous/Next: Easy Period-over-Period Comparisons

Ever tried to calculate the simple difference between the current row's value and the previous row's value? It used to involve more complex DAX functions. Now, you can use PREVIOUS and NEXT.

• Function: PREVIOUS(field) or NEXT(field)
• Example: Let's calculate the month-over-month sales growth in our table.

The formula to calculate the change from the prior month would be:

MoM Sales Change = [Total Sales] - PREVIOUS([Total Sales])

Adding this as a visual calculation directly shows the numerical increase or decrease each month compared to the previous one, without any complicated date logic.

3. Percent of Parent: Breaking Down Hierarchies

This function is invaluable when working with matrix visuals that have hierarchies (e.g., Category > Sub-Category).

• Function: PERCENTOFPARENT(field)
• Example: Let's say you have a matrix with Product Category on the rows, Product Sub-Category nested underneath it, and Total Sales as the value. You want to see what percentage of sales each Sub-Category contributes to its parent Category.

Create a visual calculation with this simple formula:

% of Parent Category = PERCENTOFPARENT([Total Sales])

This adds a column showing, for example, that "Road Bikes" make up 45% of "Bikes" sales, all calculated within the context of the matrix visual.

Best Practices for Using Visual Calculations

• Rename Your Calculations: By default, calculations get a generic name. To rename it, simply double-click the column header while you are in the calculations editor and type a new, more descriptive name.
• Remember They Are Scoped to the Visual: A visual calculation is a one-and-done analysis. It will not appear in your main Fields pane and cannot be reused on other visuals. If you need a metric in multiple places, creating a traditional measure is still the better approach.
• They Rely on the Visual's Structure: Be aware that if you remove a field from a visual (like the Month field from our examples), any visual calculation that depends on it will break. This is because they run on the data aggregated in the final visual layout.
• Use Templates for Complex Calcs: From the editing pane, you can also use one of Power BI's built-in calculation templates for running sum, moving average, and more. This is an excellent shortcut if you're ever stuck on the syntax.

Final Thoughts

Visual calculations are a fantastic addition to Power BI, bridging the gap between spreadsheet-style simplicity and the power of a proper data model. They are a powerful tool for quick, exploratory analysis, allowing you to answer business questions directly on a visual without having to write complex, model-level DAX measures every time.

Streamlining reporting in Power BI saves time, but gathering and connecting all your data is often the biggest bottleneck. This is where we built Graphed to help. We automate the entire data connection and reporting process by linking to sources like Google Analytics, Shopify, and Salesforce in one click. From there, you just use plain English to ask questions or build the dashboards you need, getting live, accurate answers in seconds without ever having to manage a dataset yourself.