6 Replies Latest reply on Apr 24, 2017 10:55 AM by Leanne Vermeulen

# Calculating Average Compared To Overall Averages For A Region Based On Selection

Hey guys,

I've got a dashboard that consists of pies showing percentage of female vs male staff per organization, divided by different staff categories.

However, given the nature of the data, each pie has its own sheet and they've simply been placed next to each other in the dashboard to create a "row".

When I click on an organization, the row of pies filters to show only information for that organization. Now what I'm looking to do is have another twos rows of pies appear beneath the current row. The first would show the number of female employees for that category compared to the average number of female employees for that category for all remaining organizations. The second would be the same but for male employees.

However, when a City is chosen in the drop-down filter, the average number of male/female employees for remaining organizations should only show results for remaining organizations in that city, if any.

Any ideas?

This second snapshot is just to show how the pies in the first row were calculated:

• ###### 1. Re: Calculating Average Compared To Overall Averages For A Region Based On Selection

Hi,

you could use FIXED LOD to achieve this. Please fine attached workbook, i did for 1 measure but same can be replicated for others.

Logic used:

1.  Create a calculated measure for "Permanent Workers Women -ALL Other Org"

SUM({FIXED [City] :SUM([Permanent Workers Women])}) - SUM([Permanent Workers Women])

2.  create calculated measure for  "% of Permanent Female Workers (Org)"

SUM([Permanent Workers Women])/(SUM([Permanent Workers Women])+[Permanent Workers Women -ALL Other Org])

3.  create calculated measure for "% of Permanent Female Workers (remaining Org)"

1- [% of Permanent Female Workers (Org)]

hope that helps.

• ###### 2. Re: Calculating Average Compared To Overall Averages For A Region Based On Selection

Hi Sunil,

Thanks for the response - the file you sent won't open - it seems to have gotten corrupted somewhere along the line. Could you possibly try uploading it again?

I tried following your instructions in the meantime, but when I place the new pies into the Dashboard and select an organization, I'm only getting values for % of Permanent Female Workers (Org) and not % of Permanent Female Workers (remaining Org).

• ###### 3. Re: Calculating Average Compared To Overall Averages For A Region Based On Selection

Hi,

It was generated using version 10.2, please try to open with 10.2, it

should work

On Apr 22, 2017 12:11 AM, "Leanne Vermeulen" <tableaucommunity@tableau.com>

• ###### 4. Re: Calculating Average Compared To Overall Averages For A Region Based On Selection

Thanks - works but still doesn't help explain why it's not working in the dashboard.

• ###### 5. Re: Calculating Average Compared To Overall Averages For A Region Based On Selection

Hi,

could you please your workbook, will check what's going wrong.

• ###### 6. Re: Calculating Average Compared To Overall Averages For A Region Based On Selection

No need - while trying to prep the file I managed to get it working. I think I must have missed something the first time round, because your solutions was correct. Thanks!