I am facing a problem in Tableau and can't seem to find a resolution for it. The problem definition requires me to provide the details. I don't have a working packaged workbook with fake data available for this but I will create one and post it later. For now, the definition is as follows:-
I am trying to create a tableau dashboard that has a simple table and a simple bar graph.
The table has account level 1 and level 2 as the dimension (it is a hierarchy) and Monthly 'Actuals' as its metric. There are different parameters like Location and Period, and for each parameter combination, the actual amounts differ. The data is straightforward until we come to the Rates calculation.
There is a third parameter where user can select Actuals or Rates. If the user selects Actuals, the above straightforward table is displayed. No issues. When an user selects Rates in the parameter, the table should flip Actuals with Rates. Rates is calculated by dividing Actuals of every account by Actual of one particular account. So say, there are four accounts - Volume A, Cost A, Cost B, Total A (these are accounts). They have their individual actual amounts. However, their actual rates become NULL, Cost A / Volume A, Cost B / Volume A, Total A / Volume A (so its Actuals divided by an Actual). It is NULL for Volume because it doesn't make sense to divide Volume by itself.
To do this, I have created a calculated field Rate to capture the Actual Amount for Volume (If Account Level 1 = 'Volume' THEN Actual ELSE 0.0 END), then another calculated field that uses EXCLUDE function on Account level 1 and Account Level 2 to sum(Rate). Then the Final Value field that displays either Actual or Rate based on the parameter condition.
The bar graph has the Actuals split by Month OR Rate split by month. Again, when the user switches between Actuals and Rates, the bar graph should respond accordingly. That has also been created using the Final Value field.
The above works fine on individual worksheets. The issue is where both these are presented in the dashboard. The table is set to act as a filter (Use as filter) so it applies to the graph. When the user clicks on Cost A or Cost B and the parameter says Actuals, it works perfectly fine. When the user selects Rate, it works fine as long as the user does not click on the accounts Cost A, Cost B, etc. As soon as the user clicks on it, the graph disappears.
I figured out that the calculated field Rate doesn't return anything when the user clicks on the account. This is because in the Rate calculated field, Volume was hardcoded. As soon as the user clicked on Cost A, this failed to return anything as the filter in the dashboard was being applied to the calculated field.
Is there a way to ensure that the calculated field always returns the Actuals for Volume irrespective of what filter action is applied on it (from the dashboard)? (The question is simple but yea, I did have to give it all to explain it. My apologies.)
I will upload a mock dashboard for this in the next 24 hours. If the above text is enough and triggers an idea, please do let me know.