Understanding Visual Calculations in Power BI: Revolutionizing Data Analysis

Power BI Visual calculations

In February 2024, Power BI introduced a game-changing feature currently in preview: Visual Calculations. This innovative addition to the Power BI suite marks a significant leap forward in data analysis and visualization, enabling users to create dynamic, on-the-fly calculations directly within visuals. This article delves into the types of visual calculations available, their applications, and how they can revolutionize the way we interact with data in Power BI.

Power BI offers various calculation tools suited for different purposes. Here's a breakdown of your options:

1. Custom Columns (Power Query):

• Create using Power Query M language.
• Similar to DAX calculated columns, but calculated at data refresh.
• Static results are stored in the model, increasing its size.
• Better for simple calculations on individual rows than aggregations.
• Best for row-level calculation. Like multiplication, static segmentation, conditional columns, etc

2. Calculated Columns (DAX):

• Use DAX formulas to define new data columns within a table.
• Static, unaffected by user interaction. This means if you select a value in the visual filter and slicer, this calculation will not change or respond to those
• Calculated and stored during data refresh, increasing model size.
• Can reference other tables and relationships, unlike visual calculations.
• Best for row-level calculation. Like multiplication, static segmentation, conditional columns, etc
• Used in slicers, filters, and visuals.

3. Measures:

• Use DAX to create dynamic calculations based on user selections.
• Static, unaffected by user interaction. This means if you select a value in the visual filter and slicer, this calculation will not change or respond to those
• Not pre-calculated, results calculated on demand.
• More efficient than storing pre-calculated results.
• Used as values in visuals or visual level filters.
• Do not increase the model size.

4. Calculated Tables:

• Create new tables based on existing data or DAX formulas.
• Ideal for intermediate calculations and storing data in the model.
• Recalculated when linked tables are refreshed.
• Can have relationships and be used in visualizations like other tables.
• When a value is selected in the visual filter or slicer, this calculation remains static and is not affected by user interaction.

5. Visual Calculations:

• A new feature that defines calculations directly on visuals.
• Easier to create and maintain than traditional DAX formulas.
• Only refer to what’s displayed on the visual, simplifying filter context.
• Combine simplicity and flexibility of calculated columns with on-demand calculation of measures.
• Offer performance benefits by working with aggregated data.

What are Visual Calculations in Power BI?

Visual calculations are DAX calculations defined and executed directly on a visual within Power BI. Unlike traditional calculations that are stored and processed in the data model, visual calculations are applied directly to the data presented in the visual, offering a new layer of flexibility and performance. This feature simplifies the DAX required for dynamic calculations, leading to easier maintenance, better performance, and more intuitive data exploration.

You can refer to my video on YouTube for Power BI Visual Calculations.

Types of Visual Calculations

Visual calculations in Power BI can be broadly categorized based on their functionality and application within visuals. Visual calculation will continue to work even if you replace the axis/row with another axis. Let’s explore the key types:

1. Running Total/Cumulative: Easily calculate the cumulative sum of a measure across a dimension, such as running total sales over time or categories.
2. Moving Average: Calculate the average of a measure over a specified window of rows, ideal for smoothing out fluctuations in time series data.
3. Percent of Total: Quickly compute the percentage that each category contributes to the total, useful for pie charts and stacked bar charts.
4. Percent of Sub Total: Quickly compute the percentage that each category contributes to the parent, useful for Matrix visual.
5. Percent of First Value: Quickly compute the percentage that each category contributes to the First Value, useful for Table, Pie, and Bar visual.
6. Period over Period Growth: Calculate the growth rate of a measure from one period to the next, essential for trend analysis. This makes time intelligence calculation easy.
7. RANGE: A special function that returns an interval of rows within a given axis, relative to the current row, allowing for flexible interval-based calculations.

How to Use Power BI Visual Calculations

To leverage visual calculations in Power BI, follow these steps:

1. Enable Preview Feature: First, ensure the visual calculations preview feature is enabled in Power BI Desktop under Options and Settings > Options > Preview features.

It may ask to close the file and open it again.

2. Choose a Visual: Select a visual in your report where you wish to apply a calculation.

3. Add Calculation: Use the “New Calculation” button in the ribbon to add a visual calculation. This opens the visual calculations window where you can define your calculation using DAX.

4. Define Calculation: Input your DAX formula in the formula bar. Power BI provides templates for common calculations like running totals and moving averages to get you started.

5. Define and Apply: Adjust your calculation as needed, considering the axis and the context of your visual. Once satisfied, apply the calculation to see it reflected in your visual.

6. Edit And Apply: In the visualization pane, if you want to modify the calculation, click on the right arrow next to the visual calculation and select Edit calculation.

Axis Parameter

The Axis parameter is an important factor in how visual calculations are performed. This parameter is optional and it determines the path taken by the visual calculation through the visual matrix. By default, it aligns with the visual's primary axis, which is usually ROWS. This means that the calculation progresses sequentially through each row in the matrix, moving from the top to the bottom. The Axis parameter can take several values, each dictating a different calculation direction:

• ROWS: This setting directs the calculation to proceed vertically, moving down through rows from the top to the bottom.
• COLUMNS: With this setting, the calculation moves horizontally across columns, from the left side to the right.
• ROWS COLUMNS: This directs the calculation to first move vertically down rows from top to bottom and then proceed horizontally across columns from left to right, effectively covering the matrix in a sequential pattern.
• COLUMNS ROWS: This setting flips the above direction, starting with horizontal calculation across columns from left to right, then moving vertically down rows, ensuring a thorough traversal of the matrix area.

Before we dive deep into the visual calculations, let us understand my model.

Model Used

In my videos and blogs, I often refer to the standard sales model that we use. It is a well-structured and comprehensive Power BI model that enables us to analyze sales data across various dimensions. The model revolves around the Sales fact table, which contains retail sales transactions. In this model, there are several components and relationships that we will explore in more detail:

Tables Used

Sales Fact Table: The core of this model, contains detailed records of retail sales transactions. Each row represents a sale, and it includes several key columns such as Order No,Sales Date, Item ID, City ID, Customer ID, Gross Amount, Discount Amount, and Net Sales. The key measure we focus on is:[Net], which represents the net sales amount calculated as Gross Amount - Discount Amount. This measure provides insights into the actual revenue generated after discounts are applied.

Dimension Tables: These tables provide context and additional details to the sales data, allowing for more nuanced analysis:

• Item: Contains information about the items sold, including Item ID, Item Name, Category, and Brand. It enriches the sales data by detailing what is being sold.
• Geography: Provides geographical context with fields like City ID, City Name, and State. This dimension helps in analyzing sales distribution and performance across different locations.
• Date: Includes date-related information such as Date, Month, Quarter, and Year. This dimension is crucial for time-based analysis, allowing for trend analysis and period comparisons.
• Customer: Contains customer-specific details such as Customer ID, Customer Name, City, and State. This dimension enables analysis of sales performance and behavior across different customer segments.

Relationships

The relationships between the Sales fact table and the dimension tables are defined as follows:

• Sales — Sales Date -> Date [Date]: This relationship links each sale to a specific date, facilitating time-based analysis and enabling the use of time intelligence functions in DAX.
• Sales [Item ID] -> Item [Item ID]: Connects sales transactions to the specific items sold, allowing for item-level analysis and category performance reviews.
• Sales [City ID] -> Geography [City ID]: Associates sales records with geographical locations, enabling regional sales analysis and geographic performance insights.
• Sales [Customer ID] -> Customer [Customer ID]: Links sales transactions to individual customers, supporting customer-centric analysis.

The relationships between the Sales fact table and the dimension tables are set as many-to-one (⋆:1), where the 'many' side is on the Sales fact table and the 'single' direction is towards the dimension tables. This setup allows for effective aggregation and filtering of sales data based on the attributes of the corresponding dimensions. This ensures that the analysis can be as detailed or as general as necessary.

Let’s take examples of each function to understand how they can be applied within Power BI visual calculations.

RUNNINGSUM

Description: Calculates a running total of a specified field along a given axis. Example: Running total of net across row.

Example Usage: RUNNINGSUM([Net])

MOVINGAVERAGE

Description: Computes the moving average over a specified window of rows for a given field. Example: Moving Average of last 2 including current row.

Example Usage: MOVINGAVERAGE([Net], 2)

RANGE

Description: Returns an interval of rows within the given axis, relative to the current row. You can utilize the Sum, Min, and Max functions on measures when performing certain visual calculations. In the following example, we are attempting to calculate the sum of the last two rows, including the current row, for the given axis, in order to get the rolling sum of the net value.

Example Usage: Calculate(Sum([Net]),RANGE(-2, TRUE, ROWS))

COLLAPSE

Description: Calculates at a higher level of aggregation without changing the context of the calculation. Example: Percent of Parent level

Example Usage: DIVIDE([Net], COLLAPSE([Net],ROWS))

COLLAPSEALL

Description: Calculates at the total level of the axis, ignoring the current context. Example: Percent of Total.

Example Usage: DIVIDE([Net], COLLAPSEALL([Net],ROWS))

EXPAND

Description: Evaluates a calculation at a lower level of the axis, providing detailed breakdowns. Example: Average of Immediate child level

Example Usage: EXPAND(AVERAGE([Net]), ROWS)

EXPANDALL

Description: Evaluates a calculation at the leaf level of the axis for detailed analysis. Example: Average from the lowest child level, city.

Example Usage: EXPANDALL(AVERAGE([Net]), ROWS)

FIRST

Description: References the first row of an axis, useful for comparisons or starting points in calculations. It can used to calculate Percent of First.

Example Usage: FIRST([Net])

LAST

Description: References the last row of an axis, useful for end-point comparisons or calculations. It can be used to calculate percent of Last.

Example Usage: LAST([Net])

PREVIOUS

Description: References the previous row relative to the current one on an axis. In below base we are getting value for last row. Same we have use for Time intelligence to get MOM, QOQ, YOY.

Example Usage: PREVIOUS([Net])

How to get MOM, QOQ, YOY Together

POP= PREVIOUS([Net],1,-1)

NEXT

Description: References the next row relative to the current one on an axis. The example shows how to get next month

Example Usage: NEXT([Net])

What happens when we change the Axis/Row

Calculations with Axis as Brand

Let us change Axis to Category

All calculations still work, because the context is an Axis, not a particular field.

Quick Templates

Templates are available to simplify the process of crafting frequent calculations in visual calculations. These templates can be accessed by clicking the fx icon on the formula bar, allowing you to easily incorporate them into your visual calculation:

• Running Sum: Utilizes the RUNNINGSUM function to compute the cumulative sum of values, incorporating each current value with those before it. RUNNINGSUN([Field])
• Moving Average: Employs the MOVINGAVERAGE function to determine the average within a specified window, dividing the total of the values by the window’s count. Moving average = MOVINGAVERAGE([Field], WindowSize)
• Percent of Parent: Uses the COLLAPSE function to find the proportion of a value in comparison to its parent’s total. Percent of parent = DIVIDE([Field], COLLAPSE([Field],Axis))
• Percent of Grand Total: Leverages the COLLAPSEALL function to calculate a value’s percentage out of the overall total. Percent of grand total= DIVIDE([Field], COLLAPSEALL([Field],Axis))
• Average of Children: Applies the EXPAND function to average the values of all child elements.
• Versus Previous: Utilizes the PREVIOUS function to compare a value with its predecessor. Versus previous = [Field] — PREVIOUS([Field])
• Versus Next: Uses the NEXT function for comparing a value with the one that follows. Versus next = [Field] — NEXT([Field])
• Versus First: Employs the FIRST function to compare a value against the initial value in the series. Versus first = [Field] — FIRST([Field])
• Versus Last: Utilizes the LAST function to compare a value with the series’ final value. Versus last= [Field] — LAST([Field])

Advantages of Visual Calculations

• Simplified DAX: Visual calculations reduce the complexity typically associated with DAX, making it more accessible for users to perform advanced data analysis.
• Easy to Create: Calculations are as easy as Excel, making them accessible for citizen developers.
• Performance: Since calculations are applied directly to the visual, they can lead to performance benefits, especially when operating on aggregated data.
• Flexibility: Users can tailor calculations specifically for the data presented in a visual, offering unparalleled flexibility in analysis.
• Dynamic Interactivity: Visual calculations respond dynamically to user interactions within the report, allowing for real-time analysis adjustments.
• Independent of Explicit Row Context: The visual calculations are not dependent on an explicit column or row axis. However, they can use the available axis to reduce the number of calculations needed.

Limitations of Visual Calculations in Power BI

While visual calculations offer a powerful and convenient way to perform calculations on visuals, it’s important to be aware of their limitations before incorporating them into your reports:

Preview Stage:

• Feature under development: Visual calculations are currently in preview, meaning they are still under development and may have limitations compared to fully released features. Functionality, stability, and performance may improve in future releases.

Supported Visuals:

Limited compatibility: Not all visual types in Power BI currently support visual calculations. Notably, certain visuals like maps, slicers, R visuals, Python visuals, and others are not compatible at the moment.

The Power BI Team has tested the following visual types and properties and found that they do not work with visual calculations or hidden fields:

• Treemap
• Map
• Shape Map
• Azure Map
• Slicer
• R visual
• Python visual
• Key Influencers
• Decomposition Tree
• Q&A
• Smart Narrative
• Metrics
• Paginated Report
• ArcGIS Maps
• Power Apps
• Power Automate
• Small multiples

Functionality:

Restricted features: Some functionalities associated with traditional measures and calculations are not yet available with visual calculations. These include:

• Filtering on visual calculations: You cannot filter data based on the results of a visual calculation, unlike measures.
• Conditional formatting: Applying visual formatting based on the value of a visual calculation is currently not possible.
• Data export: Exporting the underlying data associated with a visual calculation is not available.
• See records drill-through: The “see records” drill-through functionality, which allows you to explore the underlying data for a specific data point, is not supported with visuals using visual calculations.

Additional Considerations:

• Limited reuse: Currently, you cannot reuse visual calculations across different visuals or reports using copy-paste or other mechanisms. Each instance of a visual calculation needs to be defined individually.
• Formula editing: Editing a visual calculation requires opening the visual calculations edit mode, which can be less convenient than directly editing a measure within the modeling view. You can’t change aggregations on visual calculations
• Performance: While generally performing well, the performance of visual calculations might not always be identical to that of traditional DAX measures, especially for complex calculations.

Technical Limitations:

• Axis and Reset: Functions like USERELATIONSHIP, RELATED, or RELATEDTABLE, which rely on model relationships, cannot be used within visual calculations.
• Custom visuals: Visual calculations haven’t been extensively tested with custom visuals and may not work consistently.
• Power BI Embedded and Live Connections: Visual calculations are not currently supported in Power BI Embedded environments or with live connections to SQL Server Analysis Services.

Conclusion

Visual calculations represent a significant advancement in Power BI’s capabilities, offering users a powerful tool for dynamic and flexible data analysis. By understanding and utilizing this feature, analysts can create more intuitive, interactive, and insightful reports. As Power BI continues to evolve, features like visual calculations underscore its commitment to making data analysis more accessible and powerful for professionals across industries.

Release Blog

Visual calculations (preview)

Microsoft Documentation

Using visual calculations in Power BI Desktop - Power BI

Complete Power BI in one Video in 11 hours

Mastering Power BI: 230+ Videos

Expertise Power BI: 150+ Videos

Power BI 50 Interview Questions and 10 Advanced Use Cases

My Medium blogs can be found here if you are interested

Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.

In addition, I have over 750 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.

Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass