I'm having difficulty understanding Tableau's behavior with respect to custom formatting and evaluating a value pre/post rounding.
I have a requirement to show a business unit's performance like the below images. Formatting requirements are: Show all dollars in millions. Color a negative variance (% and $) red. Format negative values with parentheses.
I'm doing the latter with custom formats like: $0.0M;($0.0M) and using a boolean (value < 0) for the coloring.
|Unit||Dollars||LY Var $||LY Var %|
My issue arises in the case of unit A above. Where the LY Var $ is definitely negative and should show parenthesis around it. But, Tableau looks like it applies the text formatting after the rounding. I've added a copy of the LY Var without formatting to show that it's actually negative, and should have parenthesis around it.
We've discussed creating an IIF statement, converting to str etc. to get what we want, but that seems like overly complex and will require a good amount of scenario testing.
Anyone have ideas to make this work? Sample workbook attached.