Introduction to Visual Calculations in Power BI

In the last year, Power BI introduced a new feature to its ever-growing list of features in the name of visual calculations. However, this feature quickly had the heads turned towards it and the crowd talking about it. The reason is simple. Calculations in Power BI has been made simpler than ever. With using visual calculations in Power BI, you can create an advanced calculations without any technical knowledge, not even the DAX language. In this blog, we will explore visual calculations in the lights of other types of calculations in Power BI.

Let us first see what are the diverse types of calculations that are available in Power BI and how they work.

• Custom Columns in Query Editor: These columns that are created from the Power Query Editor using the M Language. They are used when row by row calculation is required like a conditional column or a column for segmentations, etc. These columns are compressed and processed better. They are stored in the data model.
• Calculated Column using DAX: These columns are created using the DAX language and are stored in the data model. They are used when row by row calculation is required or when a calculation is created from two different but related tables easily.
• Calculated Tables using DAX: These are tables that are created from the Modelling section using DAX expressions. The table can be calculated based other existing table data or even as a stand-alone table for a particular purpose. The entire table data is stored in the model and can related with other tables as well.
• Calculated Measures using DAX: Unlike the above three, the result of Measure is never stored in data model. They are calculated dynamically based on the context and filters on the visuals. They are more efficient in terms of performance as they do not increase the model size.
• Visual Calculations: Visual Calculations are directly created on the visualizations based on the data which is present on the visualizations. This gives an Excel-grid like vibe when you create a new formula by referring the values in the previously created columns. They are never stored in the data model as they are present only on the visuals. Thus, it is a more flexible and performance friendly option.

Now let us quickly understand some of the key features of Visual Calculations:

• Calculations are created directly on the visual. It is not stored anywhere on the data model.
• Calculations are based on already aggregated data on the visual. Therefore, performance is not a concern at all.
• Amazingly easy and flexible option with an excel-grid based calculation approach. It makes calculations easier for people from MS Excel Background.
• It can only create calculations based on the data present on a particular visual. Cannot refer to any table or column in the data model.

Power BI also gives quick template options where the formulas are prebuilt. Following are the template calculations that are available.

1. Running Sum: Calculates the cumulative amount of a value across a dimension.
2. Moving Average: Calculates the average of a value for selected window of period.
3. Percent of Parent: Calculates the percentage of share that is contributed by each category to its parent category.
4. Percent of Grand Total: Calculates the percentage of share that is contributed by each category to the grand total.
5. Average of Children: Calculates the average values of sub-categories against its parent category.
6. Versus Previous: Calculates the variance of a value to its preceding value in the order of the axis.
7. Versus Next: Calculates the variance of a value with its succeeding value in the order of the axis.
8. Versus First: Calculates the variance of a value with the first value in the row/column according to the axis selected.
9. Versus Last: Calculates the variance of a value with the last value in the row/column according to the axis selected.

Apart from the above options there is one more called ‘Custom’ which gives us full freedom to work with any of the DAX functions that we need to use for creating the calculations.

In short, Visual Calculations in Power BI is a game changer which will only increase the goodwill of Power BI as the people’s choice. It also reinforces its mantra as low code BI tool which helps business users without any technical knowledge. The Excel-grid like interface for calculations on the matrix also attracts more Excel users if it has not done enough already. Expecting more functionalities and formula to be added to the visual calculations feature in Power Bi in the future monthly updates.

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.