2 Replies Latest reply on Sep 11, 2016 11:01 AM by shannon Dempster

# Help!  Calculating a % with an aggregated and non-aggregated number

Hi!  I'm trying to create a % based on information from two data sources - one is an aggregated number and the other is not.

Scheduled to Actual is the main data source

RM UTIL is the secondary

I want to aggregate the scheduled hours in "Scheduled to Actual" and divide by "available hours" in RM UTIL.

For example, in September:

John Doe has 152.690 scheduled hours

I want to divide that by 176 available hours from the secondary data source.

152.69 is aggregated

176 is not

i'm looking for it to show 86.8% but it's aggregating all of the available hours to 2,088 and making it 7%

is this possible?!  I've been trying for hours!!!
!!!!!!!

• ###### 1. Re: Help!  Calculating a % with an aggregated and non-aggregated number

hi Shannon,

So yes blending is a very particular type of 'Join' (used in the loosest sense!). Here's a blog I wrote on how it works (and when/how to use it)

So any field you bring in, from a secondary source, has to come in as an aggregate. But by defining the blend fields, you can control this.

So the reason you are getting 2088 is that you only have a blend on name...so tableau creates a table (this is just a mental way of thinking about it) at that level, as per the below

An then left joins this table to the primary source, on the name...which is why we get 2088.

Actually what you want to do is create the following aggregate table

and then left Join it back on Name and Month

Had the month field been called the same, as with name, Tableau would have picked this up, but not to fear we can manually create the relationship, using the 'Edit Relationships' in the data menu

Once we have this I create the calc for %age as follows

I then bring this field in...and ensure I click the blend field on Month too

Hope that makes sense, and does what you need.

• ###### 2. Re: Help!  Calculating a % with an aggregated and non-aggregated number

Hi Simon!

Thank you so much for your help!  I was able to follow your instructions clearly and it worked! You saved me!!!