Forum Discussion
Visual Calculation to show difference when there are blank values
Hi everyone,
I'm currently building a profit and loss report but i'm facing performance issues when calculating difference between Actual and YTD using standard DAX measures, so I want to use visual calculations. Problem is, I'm getting the error 'Cannot convert value " of type text to type number'.
Here's my layout, the measure is type currency, and includes logic to convert some rows to % (removed values for obvious reasons, they aren't usually 0)
The visual calculation i'm trying to add is
Diff = [1.P&L YTD] - [1.P&L Test]
I just want to ignore all blank rows and calculate the difference between columns, and hopefully keep percentages formatted correctly as well.
Thanks for your help
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!
5 Replies
Hi wardy912
We need to keep the base measure 100% numeric and apply formatting. Can you please use this visual calculation
Diff = IF(ISBLANK([1.P&L YTD])&&ISBLANK[1.P&L Test]),BLANK(),[1.P&L YTD]-[1.P&L Test])Also, we can use dynamic format strings or individual % measure to display % rows. Please note tha visual calculations only work on numeric values and not formatted text.
Please provide sample data of records, the exact logic, and what should be the ouput if you can to work. Thank You!
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!
Your welcome wardy912
If you have any more issue, free to ask me
Hii wardy912
If this resolves your P&L performance issues and clears the conversion error, please mark this as the "Accepted Solution"
