INDEX accesses values at specific positions in the visual (positive numbers count from the start, negative from the end). ROWNUMBER returns the current row's position in the sort order.
Third Value=INDEX(3, [Sales]) Row Position=ROWNUMBER()
Output
Product
Sales
Rank
Bikes
$500K
#1
Clothing
$320K
#2
Accessories
$180K
#3
Use Cases
Custom ranking displays
Specific position lookups
Row numbering in tables
LOOKUP / LOOKUPWITHTOTALS
Fetch a specific value from anywhere in the visual matrix. LOOKUP inherits the current row's other dimensions (great for same-period comparisons); LOOKUPWITHTOTALS ignores them and pulls from the total (perfect for a fixed baseline).
-- New: June 2025 FY23 Same Q=LOOKUP([Sales], [Year], "FY23") FY23 Total=LOOKUPWITHTOTALS([Sales], [Year], "FY23")
Output (FY24 rows by Quarter)
Quarter
FY24 Sales
FY23 Same Q
FY23 Total
Q1
$120K
$100K
$420K
Q2
$140K
$110K
$420K
Q3
$130K
$105K
$420K
Q4
$160K
$115K
$420K
Use Cases
Year-over-year, same-period comparisons
Index every row against a fixed baseline
"% of [specific row]" calculations
RANK
Ranks rows by a chosen sort, optionally within partitions. Use PARTITIONBY for "rank within group" (e.g. top products per year); omit it for a global rank across the whole visual.
Navigate parent-child relationships for contribution analysis
COLLAPSE
Aggregates values to the parent level in a hierarchy. The key function for "% of Parent" calculations in matrix visuals.
Category Total=COLLAPSE([Sales], ROWS) % of Category=DIVIDE([Sales], COLLAPSE([Sales], ROWS))
Category > Product Matrix
Item
Sales
% of Category
Bikes
$500K
100%
Road Bikes
$300K
60%
Mountain Bikes
$200K
40%
Clothing
$200K
100%
Jerseys
$120K
60%
Shorts
$80K
40%
Use Cases
Contribution to parent category
Regional share of territory
Product mix analysis
COLLAPSEALL
Aggregates all the way to the grand total, regardless of hierarchy depth. Essential for "% of Grand Total" calculations.
Grand Total=COLLAPSEALL([Sales], ROWS) % of Total=DIVIDE([Sales], COLLAPSEALL([Sales], ROWS))
% of Grand Total
Region
Sales
% of Total
North
$240K
34%
South
$175K
25%
East
$140K
20%
West
$105K
15%
Central
$40K
6%
Total
$700K
100%
Use Cases
Overall market share
Total contribution analysis
Pareto (80/20) calculations
EXPAND / EXPANDALL
Drills down to child level values in a hierarchy. EXPAND moves one level down to the immediate children, while EXPANDALL goes all the way to the leaf (lowest) level.
-- EXPAND: one level down (immediate children) Child Sum=EXPAND([Sales], ROWS) -- EXPANDALL: all the way to the leaf level Leaf Sum=EXPANDALL([Sales], ROWS)
Parent Row Shows Child Detail
Category
Sales
First Child
Bikes
$500K
$300K (Road)
Road Bikes
$300K
-
Mountain Bikes
$200K
-
Clothing
$200K
$120K (Jerseys)
Jerseys
$120K
-
Use Cases
Subtotal verification
Drill-through references
Child-level aggregation checks
ISATLEVEL
Tests whether a column sits at the current row's hierarchy level. The visual-calc-native version of ISINSCOPE — guaranteed to behave correctly with EXPAND and COLLAPSE.
At Year=ISATLEVEL([Year]) YoY %= IF(ISATLEVEL([Year]), DIVIDE([Sales]-PREVIOUS([Sales]), PREVIOUS([Sales])))
YoY % Renders Only at Year Level
Row
Sales
At Year
YoY %
2023
$420K
TRUE
-
Q1
$200K
FALSE
-
Q2
$220K
FALSE
-
2024
$530K
TRUE
+26%
Q1
$240K
FALSE
-
Q2
$290K
FALSE
-
Use Cases
Show YoY / variance only on subtotal rows
Hierarchy-aware conditional formatting
Replace ISINSCOPE in visual calcs (cleaner with EXPAND/COLLAPSE)
AXIS and RESET Parameters
Control calculation direction and restart behavior
You need quick running totals, moving averages, or row comparisons. The calculation is specific to one visual. You're prototyping or doing ad-hoc analysis.
Use DAX Measures When...
The calculation needs to be reused across multiple reports. It's core business logic that should be centralized. You need complex time intelligence or CALCULATE patterns.
Governance Consideration
Visual Calculations can fragment business logic across reports. For enterprise deployments, establish clear guidelines on when they're appropriate versus centralized measures.
Performance Benchmarks
Visual Calculations operate on aggregated data, often much faster for common calculations
Visual Calculations vs equivalent DAX Measures - how much faster?
Visual Calculations run on the visual's result set, the already-aggregated data. Fewer rows to process means faster results, especially on large source tables.
DirectQuery Benefits
DirectQuery benefits the most: instead of sending complex window function queries to the source, calculations run client-side on compact results.
Performance Sweet Spot
Visual Calculations are fastest when visuals show hundreds to low thousands of rows. For very large result sets, the client-side overhead may become noticeable.
Creative Techniques & Community Patterns
Advanced patterns from Power BI experts for real-world scenarios
Dynamic Vertical Waterfall Charts
Reid Havens - Havens Consulting
Using RUNNINGSUM with stacked column charts to create dynamic waterfall visualizations, enabling segment slicing that the native waterfall chart doesn't support.
-- Creates waterfall base layer Waterfall Base= RUNNINGSUM([Value]) -[Value]
Creating reusable "template" patterns for common analytical scenarios. Apply consistent calculation logic across similar visuals with standardized naming.
-- Standard variance template Variance Abs=[Actual]-PREVIOUS([Actual]) Variance %=DIVIDE([Variance Abs], PREVIOUS([Actual]))
Template Patterns
Period comparison sets
Ranking + contribution combos
Consistent naming conventions
IBCS-Style Variance Charts
International Business Communication Standards
Create IBCS-compliant variance visualizations using Visual Calculations for absolute and relative deviations from prior periods or plans.
Visual Calculations can replace complex ISINSCOPE/HASONEVALUE patterns for hierarchy-aware conditional formatting, often cutting the code to a single line.
Use Visual Calculations as the basis for data bars and color rules. Running totals make excellent data bar sources.
Use ORDERBY for Sorting Control
Use RUNNINGSUM([Sales], ORDERBY([Sales], DESC)) to control calculation order independent of visual sort.
Reference Other Visual Calculations
Build complex analytics step-by-step: first a running sum, then a percentage of that running sum.
Test with Small Data First
Prototype on filtered data before applying to the full dataset. Makes debugging much faster.
Graduate to DAX When Needed
Start with Visual Calculations for prototyping, then convert to DAX Measures when reusability is required.
Combine with Field Parameters
Visual Calculations work great with Field Parameters for dynamic measure switching in matrix visuals.
Core Limitations
No Reusability Across Visuals
Visual Calculations exist only in their visual. To use elsewhere, you must recreate manually or convert to DAX.
Cannot Filter or Slice
You cannot filter on Visual Calculation results, use them in slicers, or apply conditional filtering.
No Data Export
Visual Calculation columns are excluded from "Export underlying data". Users won't see these in exports.
No Dashboard Pinning
Visuals with Visual Calculations cannot be pinned to dashboards. If your team relies on Power BI dashboards, replicate this logic as a DAX measure instead.
Sort Order Dependency
RUNNINGSUM, PREVIOUS, NEXT depend on visual sort order. Use ORDERBY parameter for explicit control.
Field Parameter Conflicts
If a field parameter exists in the visual, the "New visual calculation" button may disappear.
Unsupported Visual Types
Not supported in: Slicers, R/Python visuals, Key Influencers, Decomposition Tree, Q&A, Smart Narrative, Metrics, Paginated Reports, Power Apps, Power Automate, Small Multiples, Play Axis on Scatter, and Custom Visuals.
No Publish to Web
Reports with Visual Calculations cannot use Publish to Web functionality for public-facing reports.
Edge Cases & Advanced Scenarios
No Relationship Functions
USERELATIONSHIP, RELATED, and RELATEDTABLE don't work inside Visual Calculations. The visual matrix has no model context, so relationship navigation has to happen in the underlying measures.
No Self-Referencing
A Visual Calculation cannot reference itself, even indirectly through another calc that points back. Recursive logic has to be handled in DAX measures instead.
No Drill-Through or Personalization
Visuals with Visual Calculations don't support "See records" drill-through or "Personalize this visual" for end users. Stick to standard measures if either feature is required.
Platform-Specific Limitations
SSAS live connections require SQL Server 2025 or later. Power BI Embedded has no IntelliSense for Visual Calculations, so authoring happens blind. Check your target platform before relying on them.
These optional parameters control calculation direction, reset behavior, and sort order. Add them after the measure argument.
Parameter
Options
Default
Example
AXIS
ROWS, COLUMNS, ROWS COLUMNS
ROWS
RUNNINGSUM([Sales], COLUMNS)
RESET
NONE, HIGHESTPARENT, LOWESTPARENT
NONE
RUNNINGSUM([Sales], ROWS, HIGHESTPARENT)
ORDERBY
ORDERBY([Column], ASC/DESC)
Visual sort
RUNNINGSUM([Sales], ORDERBY([Date], ASC))
1 / 10
**On screen:** AE logo, title "Visual Calculations in Power BI," subtitle, ~15 min read badge.
- Welcome: Visual Calcs are a relatively new addition to Power BI that change how we write certain types of DAX
- The promise: things that used to need complex CALCULATE + FILTER patterns become one-line functions
- We'll cover what they are, every function, when to use them vs DAX measures, and the gotchas to know
*Transition:* "Let's start with the mental model."
**On screen:** "DAX Measures" card. Lives in the Semantic Model. Examples: Sales, Profit, YTD.
- DAX Measures live in the **semantic model**: shared across every report and visual
- They operate on the full data model: filter context, relationships, millions of rows
- Powerful, but every calculation has to deal with that complexity
*Transition:* "Visual Calculations work completely differently..."
**On screen:** "Visual Calculations" card. Lives in the Visual Layer. Examples: RUNNINGSUM, MOVINGAVERAGE, PREVIOUS, NEXT.
- Visual Calcs live **on the visual itself**: not in the model
- They only see what's already in the visual (the aggregated rows you can see)
- That's the key shift: small working set instead of full data model
*Key message:* DAX = model layer. VC = visual layer.
**On screen:** Small demo table with Region, Sales, Running Total, % of Total.
- Quick preview: Running Total and % of Total here are both Visual Calculations
- Sitting alongside the regular Sales measure, but computed at the visual layer
- Notice how clean it looks: that's the whole pitch
*Transition:* "Now let's go through every function you can use."
**On screen:** RUNNINGSUM card. Code: `Running Total = RUNNINGSUM([Sales])`. Demo table showing $100K → $400K cumulative.
- One-line cumulative total
- Replaces the classic `CALCULATE + FILTER + ALL` pattern you've probably written 100 times
- Use cases: YTD tracking, cumulative inventory, progressive goal achievement
*Transition:* "Next up, the smoothing function."
**On screen:** MOVINGAVERAGE card. Code: `3-Month Avg = MOVINGAVERAGE([Sales], 3)`. Demo with rolling 3-month average.
- Rolling average over N rows: second arg is the window size
- Smooths out noise so you can see the underlying trend
- Use cases: sales trend smoothing, stock prices, seasonal patterns
*Transition:* "For period-over-period comparisons, we use PREVIOUS and NEXT."
**On screen:** PREVIOUS / NEXT card. Code shows MoM Change = [Sales] - PREVIOUS([Sales]).
- Access the prior or next row of the visual
- The visual-calc replacement for OFFSET-based period comparisons in DAX
- Use cases: month-over-month variance, growth rates, trend arrows
*Transition:* "For baseline comparisons, FIRST and LAST."
**On screen:** FIRST / LAST card. Demo shows indexed growth from baseline (1.00x, 1.20x, 0.80x, 1.40x).
- FIRST = first row of visual, LAST = last row
- Great for indexed growth ratios: "everything relative to my starting point"
- Use cases: indexed growth from baseline, variance from start, latest-value references
*Transition:* "Now positional access with INDEX and ROWNUMBER."
**On screen:** INDEX / ROWNUMBER card. Demo shows ranked products with #1, #2, #3.
- INDEX accesses a specific position: positive from start, negative from end
- ROWNUMBER returns the current row's position
- Use cases: custom ranking displays, specific position lookups, row numbering
*Transition:* "Now for the killer addition from June 2025: LOOKUP."
**On screen:** LOOKUP / LOOKUPWITHTOTALS card with FY24 vs FY23 demo. Note the "New: June 2025" comment.
- Two new functions added June 2025: the most impactful update so far
- **LOOKUP** inherits the current row's dimensions: FY24 Q1 row automatically pulls FY23 Q1
- **LOOKUPWITHTOTALS** ignores them and pulls from the total: perfect for a fixed baseline
- Same-period YoY used to require time intelligence DAX; now it's one line
*Key message:* This single function replaces a lot of complex time intelligence patterns.
**On screen:** RANK card with PARTITIONBY example. Demo shows "Rank in Year" vs "Overall Rank."
- Ranks rows by a chosen sort, optionally within partitions
- `PARTITIONBY` = "rank within group" (e.g. top products per year)
- Omit it for global rank across the whole visual
- Use cases: leaderboards, top-N highlighting, YoY rank shift tracking
*Transition:* "All those work on flat lists. What about hierarchies?"
**On screen:** COLLAPSE card. Demo shows Bikes parent → Road Bikes 60%, Mountain Bikes 40%.
- Aggregates to the parent level in the hierarchy
- The function for "% of Parent" calculations in matrix visuals
- Example: Road Bikes' sales divided by Bikes' total
*Transition:* "And to go all the way to the grand total..."
**On screen:** COLLAPSEALL card. Demo shows regions as % of grand total ($700K).
- Aggregates all the way to the grand total, regardless of hierarchy depth
- The function for "% of Grand Total" calculations
- Use cases: overall market share, contribution analysis, Pareto (80/20)
*Transition:* "Going the other direction now — drilling down."
**On screen:** EXPAND / EXPANDALL card with comments showing each function's purpose.
- The opposite direction: drills DOWN to child level values
- EXPAND goes one level down, EXPANDALL goes all the way to the leaf
- Use cases: subtotal verification, drill-through references
*Transition:* "Last hierarchy function, and an important one."
**On screen:** ISATLEVEL card. Demo: YoY % renders only on Year rows (2023, 2024), blank on Q1/Q2.
- Tests whether a column sits at the current row's hierarchy level
- The **visual-calc-native** version of ISINSCOPE
- Why it matters: ISINSCOPE works in visual calcs but doesn't always play nice with EXPAND/COLLAPSE; ISATLEVEL is guaranteed to
- Classic pattern: "show YoY only on Year rows, hide it on quarter children"
*Transition:* "Now let's talk about controlling direction and restart behavior."
**On screen:** AXIS and RESET parameters reference table.
- Most VC functions accept two optional parameters: AXIS and RESET
- **AXIS** = which direction do I compute? (down rows, across columns, both)
- **RESET** = when do I restart? (never, every Year, every Quarter)
- Default is ROWS + NONE: top-to-bottom, never restart
*Transition:* "Demos will make this click."
**On screen:** "Without RESET" card. Running total flows continuously: $100K, $220K, $310K, $420K across 2023 and 2024.
- Same RUNNINGSUM, no RESET parameter
- Total flows continuously from 2023 into 2024: cumulative-since-start
- Useful when you want to see lifetime total or running progress without resets
*Transition:* "Now watch what RESET does."
**On screen:** "With RESET" card. Total resets to $90K when 2024 Q1 starts.
- Added `HIGHESTPARENT` as the RESET parameter
- Total resets to $90K at 2024 Q1: that's a true YTD
- This is the difference between **cumulative-since-start** and **YTD**
*Key message:* AXIS = direction. RESET = restart. Together they unlock most of what you'll actually want from running calculations.
**On screen:** Comparison table: Aspect | Visual Calculations | DAX Measures.
- The most common question: when do I use which?
- Walk the table top to bottom: scope, reusability, data context, complexity, performance, model impact, best for
- Key contrasts: VC = one visual / DAX = entire model; VC = aggregated matrix / DAX = full filter context
*Transition:* "Below the table are quick decision guides."
**On screen:** "Use Visual Calculations When..." card.
- Quick running totals, moving averages, row comparisons
- Calculation is specific to one visual
- Prototyping or ad-hoc analysis
*Transition:* "And when to reach for DAX instead..."
**On screen:** "Use DAX Measures When..." card.
- Calculation needs to be reused across multiple reports
- Core business logic that belongs centralized
- Complex time intelligence or CALCULATE patterns
*Transition:* "One last consideration for enterprise teams."
**On screen:** "Governance Consideration" card.
- Visual Calcs can fragment business logic across reports
- For enterprise deployments, establish clear guidelines on when VC is appropriate vs when calculation belongs in a centralized measure
- Otherwise you end up with three different "Revenue YTD" definitions across three visuals
*Key message:* VC is great for prototyping; for shared metrics, graduate to DAX.
**On screen:** Benchmark table: VC vs DAX, Import vs DirectQuery.
- Visual Calcs operate on **aggregated** data, not raw source rows
- Pareto/Cumulative: ~4x faster Import, ~16x faster DirectQuery
- Moving Average: ~5x faster Import, ~1.6x DirectQuery
- Some calcs (Highlight Max/Min) show minimal difference: not a magic bullet
*Source:* Bas Prudon, DataTraining.io, tested on 211 million row fact table.
*Transition:* "Why is this faster? Three reasons."
**On screen:** "Post-Aggregation Processing" card.
- VC runs on the visual's already-aggregated result set
- Hundreds or thousands of rows vs millions
- Fewer rows = faster, especially on large source tables
**On screen:** "DirectQuery Benefits" card.
- DQ benefits most: instead of sending complex window function queries to the source, calcs run client-side on compact results
- That's why DQ shows the biggest speedup (16x for Pareto)
- Game-changer for DQ-based reports where window queries used to be painful
**On screen:** "Performance Sweet Spot" card.
- Best in the hundreds-to-low-thousands range of visible rows
- For very large visuals, client-side overhead can become noticeable
- Not magic: pick the right tool for the data size
*Transition:* "Now let's see how the community is actually using these in the wild."
**On screen:** Dynamic Vertical Waterfall Charts card. Reid Havens, Havens Consulting.
- Use RUNNINGSUM with stacked column charts to fake a waterfall
- Why bother: the native waterfall doesn't support segment slicing; this pattern does
- Watch the tutorial link goes to AE YouTube channel
*Personal note:* This is one of my own techniques: shameless plug to subscribe.
**On screen:** Visual Calculation Templates card. Injae Park, Community Expert.
- Reusable "template" patterns for common analytical scenarios
- Standard variance template: absolute + percentage in two lines
- Apply consistent calculation logic across similar visuals with standardized naming
**On screen:** IBCS-Style Variance Charts card. International Business Communication Standards.
- IBCS-compliant variance visualizations
- Absolute and relative deviations from prior periods or plans
- Pairs perfectly with VC since variance comparisons are inherently visual-scoped
**On screen:** Simplified Conditional Formatting card. Marco Russo & Alberto Ferrari, SQLBI.
- VC can replace complex ISINSCOPE/HASONEVALUE patterns for hierarchy-aware formatting
- Often cuts the code to a single line
- The SQLBI link has more advanced patterns: highly recommend
**On screen:** Indicator Positioning Technique card. Imke Feldmann, The BIccountant.
- Line and stacked combo charts with VC for precise marker positioning
- Pairs well with Field Parameters
- Keeps the calculation logic close to the visual where it's used
**On screen:** Pareto Analysis Pattern card.
- Combine RUNNINGSUM + COLLAPSEALL for efficient Pareto (80/20) analysis
- "Running %" plus an "In Top 80%" flag
- Use cases: product contribution, customer segmentation, issue prioritization
*Transition:* "Before you start using all this, here are the limitations you need to know."
**On screen:** Pro Tip: Combine with Conditional Formatting.
- VC results make excellent data bar sources
- Running totals as the basis for color rules
**On screen:** Pro Tip: Use ORDERBY for Sorting Control.
- ORDERBY decouples calculation order from visual sort
- Useful when users can re-sort the visual but the calc needs a fixed direction
**On screen:** Pro Tip: Reference Other Visual Calculations.
- Build complex analytics step-by-step
- Layer calcs on top of each other: running sum, then percent of that running sum
- Cleaner than one giant nested expression
**On screen:** Pro Tip: Test with Small Data First.
- Prototype on filtered data before applying to full dataset
- Faster iteration when debugging
**On screen:** Pro Tip: Graduate to DAX When Needed.
- Start in VC for prototyping
- Convert to DAX Measure when reusability becomes a requirement
- VC isn't always the final answer
**On screen:** Pro Tip: Combine with Field Parameters.
- VC works great with Field Parameters for dynamic measure switching in matrix visuals
- One of the best combos in modern Power BI
*Transition:* "Now the limitations: the things that will trip you up."
**On screen:** Core Limitation: No Reusability Across Visuals.
- VC exists only in its visual
- To use elsewhere, recreate manually or convert to DAX
- This is the biggest tradeoff
**On screen:** Core Limitation: Cannot Filter or Slice.
- Can't filter on VC results, use them in slicers, or apply conditional filtering
- If you need filterable, it has to be a measure
**On screen:** Core Limitation: No Data Export.
- VC columns excluded from "Export underlying data"
- Users exporting to Excel won't see your VC results
**On screen:** Core Limitation: No Dashboard Pinning.
- Visuals with VC cannot be pinned to dashboards
- If your team relies on Power BI dashboards, replicate as a DAX measure instead
**On screen:** Core Limitation: Sort Order Dependency.
- RUNNINGSUM, PREVIOUS, NEXT depend on visual sort order
- Use ORDERBY parameter for explicit control if users can re-sort
**On screen:** Core Limitation: Field Parameter Conflicts.
- If a field parameter exists in the visual, the "New visual calculation" button may disappear
- Workaround: add the VC first, then the field parameter
**On screen:** Core Limitation: Unsupported Visual Types.
- The full list: Slicers, R/Python, Key Influencers, Decomp Tree, Q&A, Smart Narrative, Metrics, Paginated, Power Apps/Automate, Small Multiples, Play Axis on Scatter, Custom Visuals
- Sticky on tables and matrices; not so much elsewhere
**On screen:** Core Limitation: No Publish to Web.
- Reports with VC cannot use Publish to Web functionality
- Affects public-facing reports
*Transition:* "Now the edge cases: narrower scenarios but worth knowing."
**On screen:** Edge Case: No Relationship Functions.
- USERELATIONSHIP, RELATED, RELATEDTABLE don't work inside VC
- Visual matrix has no model context, so relationship navigation has to happen in the underlying measures
**On screen:** Edge Case: No Self-Referencing.
- A VC cannot reference itself, even indirectly through another calc that points back
- Recursive logic has to be handled in DAX measures instead
**On screen:** Edge Case: No Drill-Through or Personalization.
- Visuals with VC don't support "See records" drill-through
- "Personalize this visual" for end users also unsupported
- Stick to standard measures if either feature is required
**On screen:** Edge Case: Platform-Specific Limitations.
- SSAS live connections require SQL Server 2025 or later
- Power BI Embedded has no IntelliSense for VC: authoring happens blind
- Check your target platform before relying on them
*Transition:* "Let's wrap up with the quick reference."
**On screen:** Quick Reference Table: every VC function with purpose and syntax example.
- This is the page to bookmark
- Every function we covered, plus the lesser-used RANGE for window slicing
- Use it as a cheat sheet when authoring
**On screen:** Optional Parameters table: AXIS, RESET, ORDERBY.
- All the optional parameters in one place
- AXIS for direction, RESET for restart, ORDERBY for sort control
**Outro:** If you remember nothing else: VC simplifies what used to be complex DAX, but they live on the visual not the model. Prototype here, graduate to DAX when reusability matters.
- Subscribe to AE YouTube for more guides like this
- Link in the description to the full interactive guide
