7 Replies Latest reply on Sep 26, 2017 6:51 PM by Sourav Dasgupta

# Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

Hi,

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.

Rates:

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.

Many thanks,
Sourav

• ###### 1. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

If I'm following you properly, then there's no way to do what you suggest with the current setup.  Because of the Rates calculation you won't get the proper values passed through to the graph.

A mock dashboard would be great, because then it's easier to see what you've done and if there's a way to make it work the way you want.

• ###### 2. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

Attached is a dashboard with mock data. If you click on Rates in the parameter and then on the dashboard, click on any of the accounts (other than 'Volume') in the table, then the graph disappears. It doesn't if you control click both volume and any of the other accounts.

Thanks,

Sourav

• ###### 3. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

OK, I think this is what you mean, but let me know if that's not right.

• ###### 4. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

Hi Chris,

Thanks. I see the change that you have made. If I am not mistaken, you have changed the graph to show Actuals in the bar chart rather than Graph Amount Final.

That won't show me the right result as The graph amount final calculated field changes when you switch between Actuals and Rates, and that is what is expected. If you replace that with SUM(Actuals), the graph will show SUM(Actuals) no matter what I select in the parameter.

Thanks,

Sourav

• ###### 5. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

Yes, I thought that was what you were after.  If not, what do you want the graph to do when you've selected Rates in the parameter ?

• ###### 6. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

Chris,

The graph works fine and returns the numbers as expected, both for Actuals and Rates.

The problem is with the Dashboard action that makes the graph disappear when we select 'Rates' from the parameter, and then click on any of the rows in the table.

Thanks,

Sourav

• ###### 7. Re: Dashboard filter action on a dimension nullifying calculated field that is hardcoded with a value from the same dimension

I have managed to avoid this situation as you rightly said Chris, this cannot be done in Tableau. I have changed the data to have volumes and costs sit side by side in the same rows. This way I could calculate the rate and still use one sheet as a filter on the other, as described.