Shawn Wallwork Thanks. Yes my parameter has two options one sum of quantity and other sum of dollar value. I want both fields comma separated with currency additionally having $ as prefix.
I haven't been able to achieve this. Attached is the workbook for your reference.
Sales Data Dashboard_test.twbx 39.3 MB
OK, so the trick is to use the negative side of a custom number format to do a different formatting. Here's the custom format:
So positive numbers will be currency, and negative numbers will be integers. Then you'll need to change your calc to this:
if [Parameters].[Qty/Value]=1 then -[Billing Quantity]
elseif [Parameters].[Qty/Value]=2 then [Net Value]
Note the negative before the Billing field. Also this assumes that your fields will not have negative values. See attached.
Thanks a lot for your reply. The part that i couldn't figure out is "
use the negative side of a custom number format to do a different formatting. Here's the custom format:
How are you implementing this? Can you share the step by step process?
Plus in my case the qty and net value metric can be negative for items that got returned back to the manufacturer. Is there a way to do this without assuming that any of the metric stays always positive?
So the concept is that Tableau formats positive and negative numbers differently. And we can take advantage of this. The way these two different values are formatted in Tableau (and Excel) is by using formatting symbols. We use these symbols in the 'Custom' formatting option.
In this trick the most important symbol is the semi-colon ( ; ) because this is what is used to denote the separation between positive and negative formatting.
So by making 'Billing Quantity' a negative number (like I showed you in my last post) you make Tableau use the formatting on the right side of the semi-colon. All positive numbers will use the formatting on the left side of the colon.
Does this make sense? Does this answer your question?
Oops. I just read this:
Plus in my case the qty and net value metric can be negative
Sorry to say this is a deal killer. This trick won't work.