A Practical Understanding of Power BI Visual Calculations

The Visual Calculations feature in Power BI Desktop allows you to create a calculation directly on a visual by using the context on the visual. It is an easy and flexible option with Excel-grid like approach for creating calculations. In this blog we will practically see a case how the new Visual calculations feature in Power BI make life much easier for the business users of Power BI.

Let us take a case of sales data where the daily transactions are being recorded in a table and the analyst wants to calculate the Year-to-date (YTD) sales amount. Consider a case where you need to create a tabular visual with monthly sales amount along with the year-to-date sales amount, as shown below.

This can be achieved using DAX language if the user knows DAX functions. It can also be created using the visual calculations option if the user does not know DAX functions. Let us see each one of them and to understand how Visual Calculation makes it easy.

DAX Approach

Imagine you have a calendar table also. So, you would create a DAX expression as follows to achieve the ‘Year-To-Date Sales’. Now the below DAX expression might be intimidating to some business users who have no knowledge of DAX.

Now consider you are trying to achieve the same using the Visual Calculation option. You can simply click the existing table visual and click on the drop-down option for ‘Visual Calculation’ option under the home ribbon. Among the available templates, select ‘Running Sum’.

As the formula editor appears, all what you need to do is to replace the [Field] with the existing column of ‘Monthly Sales’, as shown below. Also rename the new calculation appropriately.

This shows how easy, and flexible is visual calculations in Power BI. Now let us see the advantages of visual calculation over DAX calculations.

• Ease of Use: Users doesn’t need to know DAX to create visual calculations. They can interact with the fields in the visual or even use the templates available to create visual calculations.
• Immediate Feedback: Visual calculations show the results immediately on the visual and you can verify using the visual context. DAX calculations need to be created and the added on to a visual separately.
• Data Exploration: When you are quickly exploring the data without working or finalizing the model, Visual calculation helps you perform ad hoc analysis.
• Storage on Model: Visual calculations are never stored on the model. They are calculated and stored at the visual level only.

However visual calculations also have some limitations of its own. Let us evaluate some of them as well.

• Limited Options: Visual Calculations are limited in option. For example, a DAX measure cannot be referenced inside a visual calculation.
• Limited Functionality: We cannot perform advance calculations and analysis using visual calculation, like how we do with DAX measures.
• Reusability: Unlike DAX calculations, Visual calculations cannot be reused in multiple visuals. A visual calculation created on a visual is limited to the same.
• Performance: Although visual calculation doesn’t affect the model performance as they are only calculated on the visual level, DAX is optimized for handling advanced and complicated calculations.

Conclusion

Visual Calculations in Power BI is not a substitute for DAX calculations at all. They can be better understood as an easy-to-use option for creating simple calculations. It can be seen as a stepping stone to learning advanced DAX functions for the business users without technical knowledge. Visual calculations are ideal for simple use cases and while doing quick data explorations. However, when creating more complex, reusable and performance friendly calculations, DAX could serve as a better choice.

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, Microsoft Gold Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner,  AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon ECS Service Delivery Partner, AWS Glue Service Delivery Partner, Amazon Redshift Service Delivery Partner, AWS Control Tower Service Delivery Partner, AWS WAF Service Delivery Partner, Amazon CloudFront, Amazon OpenSearch, AWS DMS and many more.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.