You've created your Big Numbers for a dashboard, but you want the client to easily identify whether the category is meeting its target. We could set the colours manually to indicate this, but this isn't a very good long-term solution as if you are working with live data numbers change all the time. Today I'll be explaining how we can do this dynamically to future-proof for any changes to the datasource.

Starting Off:

Our first step is to create a boolean calculation to return a true or false statement depending on whether targets have been met.

We can use this alone to dynamically colour our bar charts below the BANs. We will also be using this calculation as the foundation for our calcs to dynamically colour the BANs too. These calcs are shown below:

These two calculations will return the values for sales depending whether it has met it's target or not. Bringing both these onto detail and adding them to our worksheet title allows us to then colour these red and green to show positive/negative trends.

This should give you a view such as the above. Notice that both 'Sales Above Target' and 'Sales Below Target' are still in view, with 'Sales Above Target' returning as a Null value as there isn't any values for it in this view as the bar shows a negative trend. What we want is only the one relative to the view to be present. We'll have to amend our two calcs after the boolean statement from the first section to get there.

Here's where the magic happens

First we want to wrap each of our calculation in  ZN() function. This will return any Null values as the number zero.

From there we want to right click our calc, go to Default Properties>Number Format and set the number up as usual i.e. if it's a currency go to the currency tab first. Then go to the Custom tab and add another ';'. Adding a third ';' tells Tableau that we want to format any values that are zero in a specific way. Put a blank space in there after it and confirm your settings. Do this for each.

And voila! The non-relevant BAN will now be hidden in the view unless the chart's interaction with the 'Above Target' boolean calc changes in future. Otherwise it will dynamically switch to the other. Hope this helped and don't hesitate to reach out if you need anything clarifying.