Can you attach the "Anonymised Data.xlsx" spreadsheet as well. I know a way to do this and will let you know if it works out.
Will wait for your reply.
2 of 2 people found this helpful
Please find the workbook attached with the solution you want. Here's what I did:
1. I converted your data source into a 5 self unions. This would give you 5 sets of same data appended to each other. For the chart that you want, we will use one set for each measure. Whenever you do a union, Tableau automatically creates a field called 'Table Name' which can be used to distinguish between the sets.
- this is how 'Table Name' values would look like:
2. I know you would doubt the approach at this point, because all your measures are now 5x. To avoid this, we will first create a filter dimension which will be used across all your charts (except the ones where you want to show that chart in your question).
- Add the above dimension as filter to all your sheets and select True. You can see that I've added the filter to all the sheets except two. The two sheets that were exempted have the chart that you asked for and in those charts we will use all sets, whereas in all the other charts we will only use one set.
3. Create a calculated field as below, which will act as our 'Dynamic Dimension':
4. Similarly, let's create another calculated field which will act as our 'Dynamic Measure':
5. Drag these two to the columns & rows shelf. Convert your dynamic measure to Avg and CTRL+Drag it to the filters and select specieal-> non-null values.
6. Apply your formatting and colors and you should have the chart that you need.
NOTE: When you create new views, make sure that you filter out to a single set as mentioned in Step 2!!
I've attached the workbook which contains both - your old view and the new view. Let me know if this was helpful. If you think I've answered your question, then kindly mark it as the correct answer so that the thread can be closed and it will help others to take reference from.
This looks very interesting and I am keen to try it out. Unfortunately I can't open the workbook. I am working with Desktop version 18.1. Would it be possible for you to downgrade the workbook to this version?
Thanks so much. Great help.