10 Replies Latest reply on Jun 29, 2016 4:05 AM by Anoop Odedra

# Calculating % of dimension, then averaging

Hi,

Sorry I really can't share a Workbook, but I hope you can help anyway...

My data has PersonID, CompanyID, Gender, Year, Sector.

I am trying to:

• Calculate the % of females for an individual company
• Average the female percentages over all companies in a sector
• Plot the average female percentage over the years, with one line per sector

I got close by using WINDOW_AVG(sum(iif([Gender]='F',1,0))/count([Gender])), computed using Company ID.

However this only works if Company ID is also in the view, and it duplicates the sector % for every company in that sector.

Essentially all I need now is get the Company ID out of my view, but keep the pre-aggregation on a Company ID level.

Does this make sense to anyone? :-)

• ###### 1. Re: Calculating % of dimension, then averaging

Hi Oli,

Without a workbook it is difficult to visualise the issue but I will give it a go.

I think you need to use a LOD Expression. When CompanyID is not in the view you want to fix that calculation at the CompanyID level of detail.

To determine which LOD type to use, it depends what you want in each view.

Can you give some more information about what fields you would like in each of the 3 views?

Thanks

Anoop

• ###### 2. Re: Calculating % of dimension, then averaging

I know, and I'm sorry about that - would be way too much effort to clean the confidential stuff out of the data I'm afraid :/

This is only one view, not three... The chart I want is:

• Year on the x-axis
• Average % of females in sector on the y-axis
• Sector as a category, i.e. different line colours per sector

I don't need any company information. Now I could clean the data before (in e.g. Excel) and feed it in as another data source, but I'd like to learn the Tableau side of things as well :-)

What you say sounds exactly right though. The main issue is that I need to calculate % for a company first, and then average over the sector. I can't just do count(females in sector)/count(people in sector). Instead, I need to do average(count(females in company i)/count(people in company i)). Does that make sense?

Thank you!

• ###### 3. Re: Calculating % of dimension, then averaging

Hi Oli,

Yes that makes more sense now.

A LOD expression sounds ideal for what you need.

Try

{Include [CompanyID]:avg(sum(iif([Gender]='F',1,0))/count([Gender]))

I think that should give you what you are after...

Just trying to work out the logic in my head and it may work. If not, come back to me and I will have another stab at it, maybe it needs a nested LOD expression.

Anoop

• ###### 4. Re: Calculating % of dimension, then averaging

forgot to close the curly brackets in the calculation:

{Include [CompanyID]:avg(sum(iif([Gender]='F',1,0))/count([Gender]))}

• ###### 5. Re: Calculating % of dimension, then averaging

That doesn't work either... just making a pseudo calculation in superstore...

try:

avg({Include [CompanyID]:sum(iif([Gender]='F',1,0))/count([Gender])})

1 of 1 people found this helpful
• ###### 6. Re: Calculating % of dimension, then averaging

Thank you - your formula was throwing an error as it didn't like the avg, but I changed it to

{Include [Company Id]:(sum(iif([Gender]='F',1,0))/count([Gender]))}

which I then set to "Measure -> Average" and that seems to be yielding the right results!

Is this what you'd expect or did I just get lucky with my fixes?

Thank you

• ###### 7. Re: Calculating % of dimension, then averaging

That is what i was expecting, my last reply saying to put avg outside the LOD expression effectively does the same thing.

Sometimes I end up questioning my logic a few times to make sure it is right. It is difficult when you can't see the workbook but manged this time!

Anoop

• ###### 8. Re: Calculating % of dimension, then averaging

Thanks a lot!!

• ###### 9. Re: Calculating % of dimension, then averaging

Anoop sir, This is very rare skill that you have(in context of solving things without workbook). Brilliant.

-Ashish

• ###### 10. Re: Calculating % of dimension, then averaging

Years of experience working in public sector... means I can understand what people want without them showing me, as well as being able to understand problems logically (brain of a chemical and process engineer!). Top that off with deciphering a 22 month old son's speech means I can usually understand most people.

Thanks Ashish Chaudhari

And no problem for helping Oli K

Anoop