Power BI Visual Calculations- Conditional formatting
Visual Calculation magic continues
In my previous blog on visual calculations — Next-Level Power BI Visual Calculations with Offset, Index, Rank, RowNumber, and RankX — I showcased several additional functions that you can use in visual calculations.
You can find the last blog here:
In this blog, we’ll explore the enhanced feature introduced in February 2025 that allows conditional formatting to be applied using visual calculations. We’ll walk through how to integrate visual calculations into your conditional formatting logic and demonstrate how this new capability enhances the flexibility and visual appeal of your Power BI reports.
Release Notes for February 2025 that discuss this feature
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.
Model Used
For this article, I’m also using my common sales model, which includes Sales Fact, Item, Geography, Customer, and Date Dimension tables. You can find the corresponding files on my GitHub account, and you can also refer to the PBIX file where the model is already set up.
Get File from Here
PBIX file
Model View:
Where Gross Amount, COGS Amount, and Discount Amount are columns:
Gross Amount = Sales[Quantity]*Sales[Price]
Discount Amount = Sales[Gross Amount]*Sales[Discount Percent]/100
COGS Amount = Sales[Quantity]*Sales[cost] Additionally, we have the following measures:
COGS = Sum(Sales[COGS Amount])
Gross = Sum(Sales[Gross Amount])
Net = [Gross]-[Discount]
Discount = Sum(Sales[Discount Amount])
Margin = [Net] -[COGS]
Margin% = DIVIDE([Margin], [Net])
Discount % = DIVIDE([Discount],[Gross])Step-by-Step implementation of Conditional Formatting in Visual Calculations:
Let me now create a table visual that includes the Brand field from the Item table and the Margin% measure.
Then add a new visual calculation using Home Ribbon > New Visual Calculation > Versus Previous.
Versus Previous compares a value to the preceding one using the PREVIOUS function.
It will open the visual calculation pane, where we can add field to the template of Versus Previous
Add Margin% as the field in the formula
We got the difference between the current Margin% and the previous Margin%, displayed as a percentage.
In the columns in Build pane> Right click on the new visual calculation and click on Conditional formatting> Back Ground color from popup menu.
In the conditional formatting dialog, choose Format Style as Gradient and set the Field as Versus Previous.
You can now see the conditional formatting applied to the background of the visual calculation.
Click on “Back to report”, and you see conditional formatting on the visual.
Let's do conditional formatting on Margin % Column. Repeat the same step on Margin% to open the conditional formatting Dialog. This time we will choose Rule based conditional formatting
Field used for conditional formatting is “Versus previous”
Red Color Value Between -10 to 0
Orange color 0 to 0.01 and Green 0.01 to 100. As this % column, the lower and upper limits will work
Now, you can see conditional formatting on the Margin% column background as well.
Let us create a new custom visual calculation named Color which will be used to return color for Brand column (explained later in this blog).
Color = if([Versus previous] <0, “Yellow”, “Green”)
Now, let’s hide all the visual calculations.
Then let’s try applying conditional formatting to the Brand column. As before, open the Conditional Formatting dialog. In the Style option, choose ‘Field value’ and under ‘What field should this be based on?’, select ‘color’. Now, You’ll notice that even though the ‘color’ visual calculation is hidden, it still appears as an option to select. This is a capability offered by the new feature.
But here, you’ll see that the ‘OK’ button is disabled. This is because the default data type of a visual calculation is set to ‘Number’.
To make it work, we need to change it’s data type by going to the Format pane > Properties > Data Format. Under ‘Apply settings to’, select the ‘Color’ field and change the Data Type to ‘Text’.
Then, try applying conditional formatting to the Brand column and click OK.
Hope you like this blog and will try this technique in your Power BI reports, and experiment with transparency and measures to suit your data.
Looking to level up your skills in Power BI and Microsoft Fabric? Explore my free, in-depth YouTube course — designed to give you practical, real-world insights. Get started with the videos below!
Complete Course on Power BI (20 Hours)
Power BI Interview Questions — 70+ Videos
Mastering Microsoft Fabric 60+ Videos:
My Medium blogs can be found here.
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 850 videos on my YouTube channel that are related to Power BI, Tableau, and Microsoft Fabric. 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 like, share, and subscribe.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
