Paginated Report conditional formatting issue when Amount is NULL and totals are in same row scope.

[smit16299]

Hi Community,

I’m facing a conditional formatting issue in Power BI Paginated Reports (SSRS) and wanted to highlight it here to get inputs or confirm best practices.

Scenario

• I have a matrix / tablix with CUSTOM_DISPLAY_NAME as a row group.

• Rows include both detail rows (Revenue, Expenses, Operating Expenses, etc.) and calculated total rows (Total Revenue, Total Expenses, Gross Margin %, Operating Margin %, etc.).

• For total / margin rows, the dataset does not return a physical row, so Amount_Custom_IS becomes NULL (Nothing).

• I’m applying BackgroundColor conditional formatting based on:

  • Row type (detail vs total)

  • A user-selected ColorParam parameter

    Problem

    • When using IsNothing(Fields!Amount_Custom_IS.Value), formatting becomes unreliable because:

      • NULL values are produced by SSRS during rendering (not from the dataset)

      • Detail rows and total rows can fall into the same evaluation path

      • Using Fields!CUSTOM_DISPLAY_NAME.Value directly in style expressions also causes scope issues in totals/subtotals.

      • Even with InScope(), total rows like “Total Revenue” are still considered in the same row group scope as detail rows, so additional logic is required.

        Current Working Approach

        I found that the most stable approach is:

        • Avoid driving formatting purely by IsNothing()

        • Use row semantics + explicit name checks

        • Apply color only for specific total/margin rows

        • Keep all other rows white

          Example pattern:

           

          =IIF( IsNothing(Fields!Amount_Custom_IS.Value) AND ( Fields!CUSTOM_DISPLAY_NAME.Value = "Total Revenue" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Margin (%)" ), Switch( Parameters!ColorParam.Value = "Blue", "#FFFFFF", Parameters!ColorParam.Value = "Orange", "#000000", Parameters!ColorParam.Value = "Green", "#FFFFFF", Parameters!ColorParam.Value = "Violet", "#FFFFFF", True, "#FFFFFF" ), "White" )

          Key Observation

          • NULL values in Paginated Reports are often rendering artifacts, not actual business data

          • Formatting logic should be driven by row meaning, not just NULL checks

          • InScope() helps structurally, but still needs to be combined with row-name logic when totals exist in the same group

            I wanted to share this in case others are facing similar issues with financial statements / P&L reports in Paginated Reports.

            Would love to hear if there’s a cleaner or recommended pattern for handling this scenario.

            Thanks in advance!

            

            Check the Screenshot Some Values background not coming .
            
            

[v-sgandrathi]

Hi @smit16299,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @smit16299,

This behavior is normal for Paginated Reports and isn’t caused by your expression logic. Rows like Total Revenue, Gross Margin (%), and Net Income are created by SSRS during report rendering and aren’t present in the dataset. As a result, if a textbox is linked to a field with a NULL value, SSRS might skip the BackgroundColor style, leaving cells blank or without color. To address this, avoid using IsNothing() or amount-based logic for conditional formatting. Instead, set the BackgroundColor based on row identity using CUSTOM_DISPLAY_NAME, and apply it to the row or a static header cell rather than the value cell. This ensures consistent formatting, even when the amount is NULL. For financial and P&L reports, it’s best to color only semantic total or margin rows and keep detail rows white, since NULLs in totals are just rendering artifacts and formatting should reflect row meaning, not field values.

Thank you.

[smit16299]

Hello @v-sgandrathi ,
Can you explain that in little bit in detailed?

[v-sgandrathi · Accepted solution]

Hi @smit16299,

In Paginated Reports, total and margin rows like Total Revenue or Gross Margin (%) are not provided by the dataset but are generated by SSRS during rendering using group aggregates. As a result, any textbox linked to a dataset field will show as NULL for these rows. This NULL value is a rendering artifact, not missing data.

Because of this, using conditional formatting with IsNothing() is unreliable, especially when totals and detail rows are in the same row group. While InScope() can help with structure, it does not reliably identify totals in this case.

The best approach is to base formatting on the row’s purpose rather than field values. By using CUSTOM_DISPLAY_NAME or a row-type flag to specifically target total or margin rows and applying formatting at the row or static cell level, you can achieve consistent results. This method is considered best practice for financial and P&L reports.

Thank you.

[v-sgandrathi]

Hi @smit16299,

We wanted to check if you had a chance to review our last reply. Let us know if it helped or if you need more guidance, we're always happy to help further.

Looking forward to hearing from you!

Thank you.

[smit16299]

Hello @Rufyda,

Thank you for your response.

I have now updated my background color logic in the Paginated Report based on your suggestion and I am applying conditional formatting using the row identity (CUSTOM_DISPLAY_NAME) and a ColorParam parameter, as shown below:

=IIF( Fields!CUSTOM_DISPLAY_NAME.Value = "Total Revenue" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Other Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Cost of Goods Sold" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Profit (Loss)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Operating Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Profit (Loss)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Income Before Taxes" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Net Income (Loss)", Switch( Parameters!ColorParam.Value = "Blue", "#0466c7", Parameters!ColorParam.Value = "Orange", "#fbe3dc", Parameters!ColorParam.Value = "Green", "#408340", Parameters!ColorParam.Value = "Violet", "#5e4b8b", Parameters!ColorParam.Value = "Forest", "#00a86b", Parameters!ColorParam.Value = "Aritek", "#f87316", True, "White" ), "White" )

From the screenshots, you can see that for rows where the value is NULL (i.e., no data exists in the database), the background color is not being applied, and those cells appear blank or uncolored.
And also I only need colored in total values only not in Revenue, Expenses, etc.
Only need colored on Total Revenue, Total Expenses, Gross Margin %, etc.

Could you please advise how best to handle this scenario so that the background color is still applied consistently even when the underlying value is NULL?

Thank you in advance for your help.

[Rufyda]

Don’t rely on IsNothing() for totals — NULLs are often rendering artifacts.

Use row identity (CUSTOM_DISPLAY_NAME) or a RowType flag to detect totals/margins.

Apply BackgroundColor based on row meaning + ColorParam.

Default all other rows to white.

Example:

=IIF(
Fields!CUSTOM_DISPLAY_NAME.Value = "Total Revenue"
OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Margin (%)",
Switch(
Parameters!ColorParam.Value = "Blue", "#FFFFFF",
Parameters!ColorParam.Value = "Orange", "#000000",
True, "#FFFFFF"
),
"White"
)

Try these solutions and let me know if you need further clarification

Regards,
Rufyda Rahma | MIE

[smit16299]

Hello @Rufyda ,

Thank you for your response.

I have now updated my background color logic in the Paginated Report based on your suggestion and I am applying conditional formatting using the row identity (CUSTOM_DISPLAY_NAME) and a ColorParam parameter, as shown below:

=IIF( Fields!CUSTOM_DISPLAY_NAME.Value = "Total Revenue" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Other Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Cost of Goods Sold" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Profit (Loss)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Gross Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Total Operating Expenses" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Profit (Loss)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Operating Margin (%)" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Income Before Taxes" OR Fields!CUSTOM_DISPLAY_NAME.Value = "Net Income (Loss)", Switch( Parameters!ColorParam.Value = "Blue", "#0466c7", Parameters!ColorParam.Value = "Orange", "#fbe3dc", Parameters!ColorParam.Value = "Green", "#408340", Parameters!ColorParam.Value = "Violet", "#5e4b8b", Parameters!ColorParam.Value = "Forest", "#00a86b", Parameters!ColorParam.Value = "Aritek", "#f87316", True, "White" ), "White" )

From the screenshots, you can see that for rows where the value is NULL (i.e., no data exists in the database), the background color is not being applied, and those cells appear blank or uncolored.
And also I want only colored background in Total Revenue, Total Expenses, Gross Margin %, Gross Profit (Loss), etc.
Not for Revenue, Expenses, etc.

Could you please advise how best to handle this scenario so that the background color is still applied consistently even when the underlying value is NULL?

Thank you in advance for your help.