Visual Calculation to show difference when there are blank values

Hii wardy912 

To fix this, you need to ensure the visual calculation only fires when both values are numeric. You can use the ISNUMBER function or a simple COALESCE trick to handle the blank/text rows.

​Step 1: Use a Guarded Visual Calculation

​Try this formula for your visual calculation. It checks if the underlying data is numeric before attempting the math:

Diff = 
IF(
    ISNUMBER([1. P&L YTD]) && ISNUMBER([1. P&L Test]),
    [1. P&L YTD] - [1. P&L Test],
    BLANK()
)

Step 2: Fix the Formatting for Percentages

​Visual calculations typically inherit the format of the first measure referenced. Since "Gross Margin %" rows need percentage formatting while others need currency, you can use the Dynamic Format String for the visual calculation itself if you are on the latest Power BI version.

​Alternatively, wrap the calculation in a way that respects the "row type":

Diff = 
VAR _Val = [1. P&L YTD] - [1. P&L Test]
RETURN 
IF(
    ISNUMBER(_Val), 
    _Val, 
    BLANK()
)

Summary for the Community

​Visual calculations are highly sensitive to "Mixed Type" columns. If your P&L measures use "" for spacing or headers, your visual calculation must use ISNUMBER or IFERROR logic to skip those rows and avoid conversion errors.

​If this resolves your P&L performance issues and clears the conversion error, please mark this as the "Accepted Solution"!

Thanks AshokKunwar 

 The first part works, edited slightly to include % which are text values.

Diff = 
IF (
    ISNUMBER ( [1. P&L YTD] ) && ISNUMBER ( [1. P&L Test] ),
        [1. P&L YTD] - [1. P&L Test],
    IF (
        NOT ISNUMBER ( [1. P&L YTD] ),
            [1. P&L YTD],
            [1. P&L Test]
    )
)

I then formatted this as custom as follows

I used the format string
"£"#,0.00;-"£"#,0.00;"£"#,0.00;0.00%

This now works as expected! Thank you!

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!