I had this exact problem. Here is what I did.
I have this calc:
// special note: Certain [Metric] values are dollars. Others are units.
// This calc turns the dollars into negative numbers. We use a special custom formatting
// on this calc to display negative numbers with a dollar sign instead of a negative.
// It's a hack, but it's pretty cute, eh?
// the same technique is used for Target and Prior.
if attr([Metric]) = "Donations" or attr([Metric]) = "Yield per Donor" then -1*SUM([Current Total]) else SUM([Current Total]) end
So for my metrics that are dollars, I end up with a negative value. For metrics that are units, I end up with the original value. (In my report, all metrics are positive numbers, which is key for me. More on this later.)
Then in the Measures list I did right click on [Current Values], and chose "Default Properties" --> "Number Format". Choose CUSTOM format, and enter this format string:
#,###; "$ "#,###
The semicolon (I highlighted it in red) tells Tableau to make a special format for negative numbers based on the format after the semicolon. You'll probably want to do this for the % measure.
The only drawback to this hack is that if you actually have a negative percent, this will blow up on you because your negative percent will be multiplied by -1 and turned into a positive.
If that's the case, then consider creating a calc that outputs a character string, and it re-format your various measures according to the parameter chosen by the user.
Hi Joe, and thanks a lot for taking the time to help me !
I've tried what you say, turned my ratio into into a negative value but the formula for the custom format doesn't seem to work. The format won't change, even though I can see on my chart the ratio data turned into negative values.
My ratio are always positive so no problem around this.
Would you have any idea what would be wrong ?
Upload a sample workbook. At this point I would need to see something first-hand.
Let me know what version of Tableau you are using so I can mess with it in the same version.