Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: ✅ Power BI Desktop and Power BI service
A visual calculation is a Data Analysis Expressions (DAX) calculation that runs directly on a visual instead of in the semantic model. It uses the data shown in the visual, so you can create running totals, moving averages, and period-over-period comparisons without managing filter context or model relationships. Visual calculations are DAX formulas that focus on visual data rather than the entire semantic model.
This article explains how visual calculations work, how their parameters control calculation flow, and how they differ from other DAX calculations.
The following visual calculation defines a running sum for Sales Amount:
Running sum = RUNNINGSUM([Sales Amount])
A visual calculation can refer to columns, measures, or other visual calculations in the same visual.
Visual calculations differ from the other calculation options in DAX:
- You store visual calculations on a visual, not in the model. Add a model field to the visual before referring to it in a visual calculation.
- Visual calculations combine the simplicity of context from calculated columns with the on-demand calculation flexibility from measures.
- Visual calculations operate on aggregated data instead of detail-level data, so they often perform better than equivalent measures.
- Because visual calculations are part of the visual, they can refer to its structure.
For a detailed comparison of calculation options, see Use calculations options in Power BI Desktop.
Table and matrix visuals also expose visual calculations through the custom totals experience, which lets you change what the total row shows without writing DAX. For more information, see the table and matrix visual articles.
To learn how to add or edit a visual calculation, see Create visual calculations.
How visual calculations work
You create visual calculations in Edit mode, which opens over the selected visual. The edit surface has three parts: a preview of the visual, a formula bar for the DAX expression, and a visual matrix that displays results as you type.
Most visual calculations evaluate each row in the visual matrix, so aggregation functions such as SUM are usually unnecessary. Functions that depend on model relationships, including USERELATIONSHIP, RELATED, and RELATEDTABLE, aren't available.
After you add a visual calculation, it appears in the field list on the visual and renders on the visual itself.
DAX functions available in visual calculations
Many existing DAX functions work in visual calculations. Visual calculations also provide functions that act as shortcuts to DAX window functions.
| Function | Description | Example | Shortcut to |
|---|---|---|---|
| COLLAPSE | The calculation is evaluated at a higher level of the axis. | Percent of parent = DIVIDE([Sales Amount], COLLAPSE([Sales Amount], ROWS)) | N/A |
| COLLAPSEALL | The calculation is evaluated at the total level of the axis. | Percent of grand total = DIVIDE([Sales Amount], COLLAPSEALL([Sales Amount], ROWS)) | N/A |
| EXPAND | The calculation is evaluated at a lower level of the axis. | Average of children = EXPAND(AVERAGE([Sales Amount]), ROWS) | N/A |
| EXPANDALL | The calculation is evaluated at the leaf level of the axis. | Average of leaf level = EXPANDALL(AVERAGE([Sales Amount]), ROWS) | N/A |
| FIRST | Refers to the first row of an axis. | ProfitVSFirst = [Profit] - FIRST([Profit]) | INDEX(1) |
| ISATLEVEL | Reports whether a specified column is present at the current level. | IsFiscalYearAtLevel = ISATLEVEL([Fiscal Year]) | N/A |
| LAST | Refers to the last row of an axis. | ProfitVSLast = [Profit] - LAST([Profit]) | INDEX(-1) |
| LOOKUP | Evaluates an expression in the visual matrix using the current context. | LookupSalesFor2025WithContext = LOOKUP(SUM([Sales]), [Year], "2025") | N/A |
| LOOKUPWITHTOTALS | Evaluates an expression in the visual matrix with totals. | LookupSalesFor2025WithTotals = LOOKUPWITHTOTALS(SUM([Sales]), [Year], "2025") | N/A |
| MOVINGAVERAGE | Adds a moving average on an axis. | MovingAverageSales = MOVINGAVERAGE([Sales Amount], 2) | WINDOW |
| NEXT | Refers to a next row of an axis. | ProfitVSNext = [Profit] - NEXT([Profit]) | OFFSET(1) |
| PREVIOUS | Refers to a previous row of an axis. | ProfitVSPrevious = [Profit] - PREVIOUS([Profit]) | OFFSET(-1) |
| RANGE | Refers to a slice of rows of an axis. | AverageSales = AVERAGEX(RANGE(1), [Sales Amount]) | WINDOW |
| RUNNINGSUM | Adds a running sum on an axis. | RunningSumSales = RUNNINGSUM([Sales Amount]) | WINDOW |
Parameters that control calculation flow
Some visual calculation functions accept optional parameters that control how the calculation traverses the visual matrix:
- Axis and Reset are specific to visual calculations. They reference the visual structure rather than specific fields.
- OrderBy and PartitionBy also work in calculated columns and measures. They reference fields directly.
The following sections describe each parameter and when to use one over another.
Axis
Many functions have an optional Axis parameter, which you can use only in visual calculations. The Axis parameter influences how the visual calculation traverses the visual matrix. The Axis parameter defaults to the first axis in the visual. For many visuals, the first axis is ROWS, which means that the visual calculation is evaluated row-by-row in the visual matrix, from top to bottom. The following table shows the valid values for the Axis parameter:
| Axis icon | Axis name | Description |
|---|---|---|
|
ROWS | Calculates across rows. |
|
COLUMNS | Calculates across columns. |
|
ROWS COLUMNS | Calculates across rows, and then columns. |
|
COLUMNS ROWS | Calculates across columns, and then rows. |
| ROWPAGES | Calculates across small multiples. | |
| ROWS ROWPAGES | Calculates across rows, and then small multiples. | |
| COLUMNS ROWPAGES | Calculates across columns, and then small multiples. | |
| ROWPAGES ROWS | Calculates across small multiples, and then rows. | |
| ROWPAGES COLUMNS | Calculates across small multiples, and then columns. | |
| ROWS COLUMNS ROWPAGES | Calculates across rows, columns, and then small multiples. | |
| ROWS ROWPAGES COLUMNS | Calculates across rows, small multiples, and then columns. | |
| COLUMNS ROWS ROWPAGES | Calculates across columns, rows, and then small multiples. | |
| COLUMNS ROWPAGES ROWS | Calculates across columns, small multiples, and then rows. | |
| ROWPAGES ROWS COLUMNS | Calculates across small multiples, rows, and then columns. | |
| ROWPAGES COLUMNS ROWS | Calculates across small multiples, columns, and then rows. |
Visual calculations support visuals that use small multiples. In the visual matrix, the field in the Small multiples field well appears on the ROWPAGES axis. Use ROWPAGES to calculate across the small multiples, or combine it with ROWS and COLUMNS to control the traversal order.
Note
If you specify an axis that isn't present on the visual, the calculation ignores that axis.
Reset
Many functions include an optional Reset parameter that you can use only in visual calculations. This parameter partitions the target column to determine when the function resets to 0 or changes scope while traversing the visual matrix.
The default value is NONE, which means the calculation doesn't reset.
The Reset parameter accepts these values:
- An integer.
- A column reference.
- A synonym: HIGHESTPARENT, LOWESTPARENT, or NONE.
Each value specifies a target level in the visual calculation hierarchy. The calculation interprets this level in absolute or relative mode.
An integer's sign determines the mode. Positive values use absolute mode, negative values use relative mode, and 0 doesn't reset the calculation. The synonyms provide equivalent values.
A column reference uses absolute mode.
Absolute reset mode
Absolute mode partitions by the target column and all columns above it. If the target column isn't present at a level, the calculation partitions by the remaining available columns.
A positive integer identifies the target column from the top of the hierarchy. The top column is 1, the next column is 2, and the maximum value is N, the number of columns in the hierarchy. Values greater than N use N. You can also specify the column directly.
For example, consider a visual calculation with these hierarchy levels: Year, Quarter, Month, and Day. The following table shows how the calculation partitions at each level depending on the value of Reset:
| Level / value | Reset = 1 or Year | Reset = 2 or Quarter | Reset = 3 or Month | Reset = 4 or Day |
|---|---|---|---|---|
| Day level | Year | Quarter and Year | Month, Quarter and Year | Day, Month, Quarter and Year |
| Month level | Year | Quarter and Year | Month, Quarter and Year | Month, Quarter and Year |
| Quarter level | Year | Quarter and Year | Quarter and Year | Quarter and Year |
| Year level | Year | Year | Year | Year |
| Grand total level | None | None | None | None |
Relative reset mode
For a negative integer -X, relative mode partitions by columns that are X or more levels above the current level. If no column meets that condition, the calculation doesn't partition. Valid values range from -1 through -N+1, where N is the number of columns in the hierarchy. Values below that range use -N+1.
The following table shows relative mode for a hierarchy with Year, Quarter, Month, and Day:
| Level / value | Reset = -1 | Reset = -2 | Reset = -3 |
|---|---|---|---|
| Day level | Month, Quarter and Year | Quarter and Year | Year |
| Month level | Quarter and Year | Year | None |
| Quarter level | Year | None | None |
| Year level | None | None | None |
| Grand total level | None | None | None |
Reset parameter synonyms
The Reset parameter also provides the following synonyms:
- NONE is the default value. It doesn't reset the calculation and is equivalent to 0.
- HIGHESTPARENT performs an absolute reset by the highest level and is equivalent to 1.
- LOWESTPARENT performs a relative reset by the immediate parent and is equivalent to -1.
Examples of using the Reset parameter
For a hierarchy with Year, Quarter, Month, and Day, the following calculations are equivalent. They return the sum of Sales Amount and restart for each year at every evaluation level:
RUNNINGSUM([Sales Amount], HIGHESTPARENT)
RUNNINGSUM([Sales Amount], 1)
RUNNINGSUM([Sales Amount], [Year])
The following calculations restart the sum of Sales Amount at 0 for each immediate parent. The parent depends on the evaluation level:
RUNNINGSUM([Sales Amount], LOWESTPARENT)
RUNNINGSUM([Sales Amount], -1)
The following calculation doesn't reset. It adds each day's Sales Amount to the previous values:
RUNNINGSUM([Sales Amount])
OrderBy and PartitionBy
The OrderBy parameter accepts ORDERBY, and the PartitionBy parameter accepts PARTITIONBY. Unlike Axis and Reset, you can use these parameters in calculated columns, measures, and visual calculations, and they refer to fields directly.
Not all functions provide these parameters. Visual calculation-exclusive functions don't provide a PartitionBy parameter. Window functions only provide a Reset parameter if you use them in a visual calculation. The Relation parameter on window functions is available whether or not you use it in a visual calculation, but it only accepts an Axis if you use it in a visual calculation.
Choose between these parameters
You don't need to specify all of these parameters, and you can combine them. To decide which parameter to use:
- Prefer Axis and Reset when you want field-agnostic behavior that follows the visual structure. Referring to the visual structure is more flexible than explicitly referencing fields.
- Use OrderBy and PartitionBy when you want to pin the calculation to specific fields regardless of the visual layout.
- Use PartitionBy instead of Reset when the axis has one level. Reset requires multiple levels, so it doesn't work with one field or with multiple fields in a single level.
Parameter pickers
Use parameter pickers to select values for visual calculation function parameters. The following image shows the Look up a value with totals template:
To activate parameter pickers, press Ctrl+Space.
Visual calculation templates
Visual calculations include templates to make it easier to write common calculations. The template button in visual calculations edit mode opens a menu of available templates.
The following templates are available:
- Running sum. Calculates the sum of values, adding the current value to the preceding values. Uses the RUNNINGSUM function.
- Moving average. Calculates an average of a set of values in a given window by dividing the sum of the values by the size of the window. Uses the MOVINGAVERAGE function.
- Percent of parent. Calculates the percentage of a value relative to its parent. Uses the COLLAPSE function.
- Percent of grand total. Calculates the percentage of a value relative to all values, using the COLLAPSEALL function.
- Average of children. Calculates the average value of the set of child values. Uses the EXPAND function.
- Versus previous. Compares a value to a preceding value, using the PREVIOUS function.
- Versus next. Compares a value to a subsequent value, using the NEXT function.
- Versus first. Compares a value to the first value, using the FIRST function.
- Versus last. Compares a value to the last value, using the LAST function.
- Look up a value with context. Finds a value or evaluates an expression on the visual matrix within the current context. Uses the LOOKUP function.
- Look up a value with totals. Finds a value or evaluates an expression on the visual matrix with totals. Uses the LOOKUPWITHTOTALS function.
Hide fields on the visual
In visual calculations edit mode, you can hide fields from the visual just like you hide columns and tables in the modeling view. For example, to show only the Profit visual calculation, hide Sales Amount and Total Product Cost from view.
Hidden fields remain in the visual matrix, so visual calculations can still refer to them. They don't appear in the resulting visual. Include hidden fields only when your visual calculations require them.
Format a visual calculation
Format a visual calculation with data types, formatting options, or a custom visual-level format string. The Data format options in the General section of the formatting pane control the format.
Considerations and limitations
Unsupported visual types
Visual calculations and hidden fields don't work with the following visuals:
- Slicer, R visual, Python visual
- Key Influencers, Decomposition Tree, Q&A, Smart Narrative
- Metrics, Paginated Report, Power Apps, Power Automate
- Custom visuals
Unsupported features
The following features aren't available with visual calculations:
- Filtering, sorting, or changing aggregations
- Self-referencing (a calculation can't refer to itself)
- Copy/paste or reuse across visuals
- Data categories, data limits, or show items with no data
- Dynamic format strings (can't set or use as format string)
- Personalization of visual calculations or hidden fields
- You can set alerts on visual calculations, but changes to visual calculation values don't trigger the alerts.
- See records drill-through
- IntelliSense in Power BI embedded
Publishing and sharing limitations
- Can't pin to dashboards
- Can't use Publish to web
- Data exports exclude visual calculation results; hidden fields only appear in underlying data exports
Other limitations
- Field parameters work with visual calculations but have some limitations.
- Live connections to SQL Server Analysis Services require version 2025 or later.
Related content
- Create visual calculations
- Training module: Create visual calculations in Power BI Desktop
- Use calculations options in Power BI Desktop
- Create measures for data analysis in Power BI Desktop
- Create and format table visualizations in Power BI - includes custom totals, which are powered by visual calculations.