You hit the nail on the head when you said "My problem is that when I use a user filter, it only shows data for that user" -- how can we get around this? Well there are a number of ways...
1) Duplicate your data source and blend in the unfiltered, aggregated data. You can even do an extract and aggregate the data up so that the individual salesman performance is obfuscated.
2) Use pass-through functions. If you're connected to a DB then you can get the total averages for each level by sending a query direct to the database and ignoring Tableau's filters
3) Use table calculations (my favorite). Turn your user filter into a table calculation, and then it won't be filtering out the data at the query, only after the data is returned to Tableau -- thus you can make use of further table calcs (TOTAL) to aggregate the average up to another level - Office and Region.
Unfortunately I can't post a full solution workbook as an example because as soon as I start using User Filters, it's specific to my Tableau Server and won't work on yours so here goes a step-by-step guide:
a) Open the attached and go to Sheet 3, which has all the TOTAL calculations set up
b) Create your user filter as normal
c) Create a calculated field that uses your User Filter - something like
MIN(IF [User Filter 1] = TRUE THEN 1 ELSE 0 END)
Call it 'Filter me' or something
d) Add this calculated field to the filter shelf and select 1 as the filter option.
e) Now your data should be filtered to just the view you see! HOWEVER, when you change the person logged on (from the bottom right) the view does not update until you hit F5 -- I think this is a BUG
f) You can get around this by creating a second User Filter where All Users can see all the data (just pick one dimension and select all the options) and then add this to the filter shelf too. This won't filter anything out, but it will force Tableau to refresh when the user changes.
Phew! After all of that, perhaps option 1 would have been easier
I can't quite get the table layout you have in your desired example, i.e. the column names are the name of the relevant office, or region, but I think it's gives the same message? If all goes well, you should end up with something like in the screenshot.
Hope that helps,
Thanks for your help. I went with option 2. I ended up doing a custom SQL connection where I used oracle analytic functions
(ie. avg(sales) over (partition by region) as avgRegSales).
The query ended up being quite slow, but once the extract was made, everything was fine. I appreciate the time you took to prepare such a thoughtful response.
I am facing similar issue with my data set.
I have sales for 4 regions: NA, EMEA, LATAM, APAC and there 4 different user/owners of each region. One user doesn't have access rights of other regions. But at the same time, we have to show their region sales as % of total sales. When I create a user filter, then both regional and total sales are filtered. Is there any way around this.
Can we create any table calculation to achieve this.
Did you find any solution to your problem. Can you please explain how you worked it out.
Thanks & Regards,