conditional Formatting in Calculation Item

[Mniknejad]

Hi, I’m looking for help with formatting in a Power BI matrix using calculation groups.

Report design

• Matrix has one visible measure: KPI_Value_Visible_CAD

• KPI rows come from a KPI dimension (Net Sales, Traffic, AOV, Conversion, etc.)

• A Scenario calculation group creates the columns: Actual, LE, LY, LE % Var, LY % Var

• Time items like Yesterday, WTD, MTD, QTD are also used (another calc group or logic)

• All base KPI measures return numeric values

What I need

• Only for LE % Var and LY % Var:

  • if the value is negative, show red font and parentheses

  • otherwise show black

• Other scenario columns should stay normal

Problem

• Conditional formatting does not work reliably for these calc items in the matrix

• Dynamic format string also does not apply as expected

• When the Scenario calculation group is applied, ISNUMBER(KPI_Value_Visible_CAD) returns FALSE

• If I remove the Scenario calculation group from the matrix, ISNUMBER returns TRUE

It looks like the calculation group is causing the final evaluated value to be treated as text in the matrix, which blocks numeric color formatting.

Question
Is there a supported way to format specific calculation items (LE % Var / LY % Var) as red when negative in a matrix that

[v-pnaroju-msft]

Hi Mniknejad,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[sarahlns]

Hi, this is expected behavior when using calculation groups.

When a calculation group with dynamic format strings is applied, the value rendered in the matrix is no longer exposed as a pure numeric value to the visual. That’s why ISNUMBER() returns FALSE and why standard conditional formatting rules become unreliable for specific calculation items like LE % Var and LY % Var. This is a known limitation, not a bug.

Power BI does not support per-calculation-item conditional formatting directly in a matrix.

Supported pattern

The only reliable approach is to separate value logic from formatting logic:

1. Keep the visible KPI measure purely numeric (no formatting logic).

2. Use dynamic format strings in the Scenario calculation group to handle:

   • Percent formatting

   • Parentheses for negative values

   • Drive font color using a separate color measure and apply conditional formatting using Field value.

     Example:

      

     KPI Color := VAR Scenario = SELECTEDVALUE ( 'Scenario'[Scenario] ) VAR Value = [KPI_Value_Visible_CAD] RETURN IF ( Scenario IN { "LE % Var", "LY % Var" } && Value < 0, "#C00000", "#000000" )

     Then apply Font color → Conditional formatting → Field value based on this measure.

     This pattern:

     • Works reliably with calculation groups

     • Preserves correct totals

     • Is fully supported

       Per–calculation-item conditional formatting is currently not supported in Power BI matrices.

[v-pnaroju-msft]

Hi Mniknejad,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

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:

1. 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.
2. 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.

[Mniknejad]

unfortunatly none of them are working Power BI does not fully support conditional formatting when multiple calculation groups are applied to matrix columns; the calculation context is lost during rendering

[cengizhanarslan]

In a calc-group matrix, ISNUMBER() often looks “false” because the cell is getting a formatted value (or the measure is being coerced), even though the underlying value is numeric. So use SELECTEDMEASURE() + calc-item detection.

Create one measure just for formatting:

Font Color (Scenario) =
VAR v = SELECTEDMEASURE()
VAR scen = SELECTEDVALUE ( 'Scenario'[Scenario] )   -- your calc group column name
RETURN
IF (
    scen IN { "LE % Var", "LY % Var" } && v < 0,
    "#C00000",
    "#000000"
)

Then apply it in the matrix:
Conditional formatting → Font color → Format by: Field value → Font Color (Scenario).

For parentheses, do it with a dynamic format string on the calc items (not with FORMAT() in the measure), e.g. on LE % Var / LY % Var items set a format like:

#,0.0%;(#,0.0%)

That gives parentheses automatically for negatives while keeping the value numeric, so conditional formatting still works.

If you currently have FORMAT() anywhere in the calc items (or in KPI_Value_Visible_CAD), remove it — that’s the #1 reason the matrix treats the result as text and blocks numeric-driven formatting.

[burakkaragoz]

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.

Kod snippet'i

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

1. Select your Matrix visual.

2. Go to the Format pane > Cell elements.

3. Turn on Font color.

4. Click the fx icon.

5. In "Format style", select Field value.

6. 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.

[MFelix]

Hi @Jackryan360 ,

When you start to working with Calculation Groups this can really be difficult to start. I advise you to follow this video from @parry2k , it's one of the best tutorial that I seen since the beginning of calculation items availability.

In the same You tube channel you have more on options for calculation groups.