Forum Discussion
conditional Formatting in Calculation Item
Thankyou, MFelix, burakkaragoz and cengizhanarslan for your responses.
Hi Mniknejad,Thank you for the update.
Based on my understanding, when multiple calculation groups are placed on Matrix columns in Power BI, the calculation item context is not preserved during visual rendering. Conditional formatting is evaluated after the DAX query completes, by which time the identity of specific calculation items is no longer reliably available. Consequently, visual level conditional formatting cannot consistently determine which calculation item produced a given value, and therefore may be unreliable in this scenario.
Please consider the following workarounds, which may help resolve the issue:
- Move scenario logic out of calculation groups on columns. Instead, use explicit measures or a Scenario dimension. Limit to at most one calculation group affecting columns. This preserves formatting context and ensures reliable behavior.
- Split the matrix into separate visuals. One for base values and another for variance values that require conditional formatting. This approach avoids loss of calculation group context at render time.
If you require calc-item-scoped conditional formatting across multiple calculation groups, please raise an idea on the Ideas forum using the link:Fabric Ideas - Microsoft Fabric Community
We hope this information helps resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
Hi Mniknejad ,
You have correctly identified the root cause: Data Type Conversion.
If ISNUMBER(KPI_Value_Visible_CAD) returns FALSE when the Calculation Group is active, it means your Calculation Item expression is likely using the FORMAT() function (e.g., FORMAT(SELECTEDMEASURE(), "0.0%")). This converts your numbers into Text, which breaks standard numeric conditional formatting and aggregation.
Here is the supported way to fix the data type issue and apply the specific Red/Parentheses formatting you need:
Step 1: Fix the Calculation Item (Return Number, Not Text) Go to your Calculation Group (in Tabular Editor or Power BI Model View).
Expression: Ensure the expression for "LE % Var" and "LY % Var" returns a pure number.
Correct: DIVIDE( ... )
Incorrect: FORMAT( DIVIDE( ... ), "0.0%" )
Format String Expression: Use this property to handle the visual display (parentheses). Enter a custom format string that handles negatives automatically.
Example: "0.0%;(0.0%);0.0%" (Positive; Negative; Zero).
Note: The semi-colon syntax tells Power BI to wrap negatives in parentheses automatically without changing the underlying value to text.
Step 2: Create a Dynamic "Color Measure" Since standard conditional formatting rules struggle with the dynamic context of Calculation Groups, you should create a dedicated DAX measure to handle the logic.
KPI Font Color =
VAR CurrentScenario = SELECTEDVALUE('Scenario'[Name]) -- Replace [Name] with your column
VAR CurrentValue = [KPI_Value_Visible_CAD]
RETURN
SWITCH(
TRUE(),
-- Logic for Variance columns
CurrentScenario IN {"LE % Var", "LY % Var"} && CurrentValue < 0, "Red",
-- Default color for everything else
"Black"
)Step 3: Apply "Field Value" Formatting
Select your Matrix visual.
Go to the Format pane > Cell elements.
Turn on Font color.
Click the fx icon.
In "Format style", select Field value.
Select the [KPI Font Color] measure you created in Step 2.
Why this works:
Step 1 ensures the matrix sees a Number, so sorting and performance remain intact.
Step 2 & 3 force the color change based on the specific intersection of the "Scenario" and the Value, bypassing the limitations of the standard UI rules.
Hope this gets your matrix looking professional!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.