I'm creating a Tableau Dashboard with 'buttons' which are coloured red or green based on certain criteria and what is selected in the filters. The filters are just a way to select different offices in different regions and when selecting an office the buttons should change colour depending on whether the targets for the different metrics have been hit for that office or not.
The navigation buttons on Tableau won't accommodate this so I've made a work around. For each 'button' I've created a worksheet with just the text of the metric name on the Label mark and a calculated field on the colour mark. I've then added the worksheet to the Dashboard and added an action to go to the corresponding metric dashboard when the 'button' is clicked on.
The issue I'm having is the conditional colouring of one of these metrics. This metric is based on stock levels. For each office there are multiple categories of stock types, each with a corresponding target, with multiple 'bins' in each category. I want the button to turn red if ANY of the combined total of stock in the bins for one category is over the target for that category for that office.
To try and type it logically- For the currently filtered data: IF EXISTS(FOR EACH OFFICE( FOR EACH CATEGORY: [SUM(BinValue)< CategoryTarget])) THEN 'Green' ELSE 'Red'
I've tried to translate that logic into Tableau's functions in a calculated field and have the following: SUM(INT({INCLUDE [Category]:Min([CategoryTarget])} > {INCLUDE [Category]:SUM(BinValue)}))
This colouring is correct when I add the Office Name and Category pills to the worksheet to test my logic however when I remove the pills the colouring isn't correct. Something seems to be going wrong when I try to sum the number of categories that are within target levels over all offices and targets.
I've tried so many iterations of the following functions and have been going around in circles for days now: INCLUDE, EXCLUDE, FIXED, IF, SUM, INT
If anyone knows how to do this properly or even just a different way of being able to conditionally colour buttons on a dashboard I would be incredibly grateful.
The structure of my data is as follows with some dummy data as an example:
| Region | SubRegion | Office | Category | Bin | BinValue | CategoryTarget |
|---|---|---|---|---|---|---|
| North | NorthWest | Manchester | Toys | B123 | 30 | 50 |
| North | NorthWest | Manchester | Toys | B456 | 40 | 50 |
So for a Stock Level metric selecting any of ALL/North/NorthWest/Manchester filter options should flag as red due to the total of the bins in one category in an office being higher than the target amount for that category for that office.
I've updated my calculated field however I'm still having issues with the grouping showing as true/false correctly. This is what it is now: MAX( {INCLUDE Category, Office:Sum(BinValue)} > {INCLUDE Category, Office:MIN(CategoryTarget)} ) With True showing as Red and False Green (we want to be below target hence the green).
When working on the example to showcase the issue I managed to get it working.
I ended up using the following logic: max({EXCLUDE [Bin]:SUM([Bin Value])} > [Category Target])
This meant that even if most of the Offices in the filter were within their stock level targets, if there was one with stock levels over target the 'button' showed as red.
I published the example I've used anyway in case it helps others in the future. Link to the Tableau Public dashboard: https://public.tableau.com/views/ConditionalColouring/Dashboard1?:language=en-GB&:useGuest=true&:display_count=y&:origin=viz_share_link
Thank you very much for the help!