Thanks for your prompt response, and for presenting the data in a clearer way.
What I'm looking to do is - create a calculated measure such that: eg. for Region B in Feb, A Costs +B Costs will always be 145 + 163 + 1981 = 2,295, regardless of whether the "Yes?" filter is set on "Yes", "No", or "All".
Let me know if this makes any more sense - thanks again!
[I assume you got your arithmetic wrong - 145+163+1981 = 2289?]
Check out the attached. There is a parameter which switches the Output sheet from this:
How is this done? Well - it looks like you were on the right track with the TOTAL calcs. Some tips:
1. Do as many calcs in the primary data source as you can - even if they are operating on the secondary data source
2. I used a parameter as my filter - check out the [New yes or no] calc. That's an ATTR() on the Level of Detail. What does this mean? Well, I can add it and hide values rather than filter them ( move it to the rows, right-click and choose "Show hidden data" to see all the values). Hiding instead of filtering is the key when doing this kind of task.
3. Partitioning is also important in this example!
Thanks alot. I'm amazed at how complex the solution is!
While I can't say that I can understand what exactly is going on, it seems to serve my purpose, so thanks alot! One thing I do need to know though is how the Yes? parameter works, and the "New Yes or No". The reason is that I'd like to be able to filter my data such that I only display "Yes and No" (in one column, not two separate ones), "Yes" and "No". Currently the solution only displays "Yes and No" in two separate columns, or only "Yes", but not only "No".
1 of 1 people found this helpful
Yes - this kind of thing can appear complex. It's partly because of the way Tableau uses filters. Once you untick something from a filter, the query Tableau sends to the underlying dataset explicitly excludes those records. As a result, when you filter out "No" records, they simply aren't in the result query, so Tableau cannot use them in totals.
Using Parameters and hidden data is a useful technique to get around this. Yes, it's not straightforward, but a useful trick to have in your arsenal!
How does it work?
Check out the help file on parameters for more info about how they work:
The [New yes or no] calc looks at the parameter and either returns all Yes/No as "yes" or their actual values. Because I have HIDDEN the No results, the output table appears to be "filtering" the results as you change the parameter. In reality, the results are still there, but the hidden data is done as the last thing before drawing the table, giving you the result you want.
Thanks Andy. I will do my homework and figure out how all this works!