8 Replies Latest reply on Nov 10, 2016 4:08 AM by yogesh sawant

Creating a calculated field with measures from 2 different data sources

Hi all,

I have a single workbook with 2 data sources. I want to create a calculated field that uses measures from each of the 2 data sources. The problem I keep having is that when I write out the calculated field, I keep getting an error that says 'cannot mix aggregate and non aggregate functions'.

Both measures are 'number' types but it just won't seem to recognize the function.

What I'd like to do is divide the measure from one data source, by the measure from the other.

Any ideas?

Thanks

Jag

• 1. Re: Creating a calculated field with measures from 2 different data sources

Yes when you use data from different data sources in your calcs you have to put all the measures (and dimensions) in aggregate form.   Example Sum([Measure 1])/sum([DataSource 2].[Measure 2])

• 2. Re: Creating a calculated field with measures from 2 different data sources

Hi John,

While your solution works and the calculated field works (thanks) I need to be able to see the results as a 'count'. I believe this means the resulting calculated field shouldn't be in aggregate form.

Is there any way I can create the calculated field so the result is not in aggregate form?

Thanks

Jag

• 3. Re: Creating a calculated field with measures from 2 different data sources

Jagdeep,  Count is an aggregation, so I'm not following.

• 4. Re: Creating a calculated field with measures from 2 different data sources

Hi John,

Whenever I put the new calculated field into a row or column, and then group by a dimension, I keep getting '0' values for everything. So it doesn't look like the calculation is pulling through.

I wasn't sure if I need to dis-aggregate the data?

• 5. Re: Creating a calculated field with measures from 2 different data sources

Hi all,

To add some clarification to my original question, the following are some screenshots of what I am working with.

So what I need to do is divide the values in the middle column (% of total count of Oct...) by the values in the column on the right (% of total population).

The middle column (% of total count...) was created using the following calculated field;

So both are considered as 'measures' within my data tab as follows;

% of total count of cars = CNT

% of total population = SUM

Each columns comes from a separate data source, so I have connected 2 separate data sources within the worksheet.

I have tried creating the following calculated field;

But I keep getting the error message 'cannot mix aggregate and non-aggregate functions'. So I've then turned the 'Octavia Sales within AOI' into an aggregate by using the SUM function as follows;

Whilst this calculation comes up as valid, when I add the new field onto the sheet into the same table as 'life stage band', the second column doesn't get populated with any figures. The Pill for the new field is AGG.

Any ideas on how I can solve this problem and what calculated field I need to create to get the columns to divide?

Thanks

Jag

• 6. Re: Creating a calculated field with measures from 2 different data sources

Hello Jagdeep,

Could you please upload the workbook with some sample data. that would help understand the scenario better.

-Yogesh

• 7. Re: Creating a calculated field with measures from 2 different data sources

Hi Yogesh,

Please find a sample of the data below - it's on my Tableau public profile. It's the data on sheet 2 that I'm having trouble with in dividing the middle column (% count of sales...) with the column on the right (% population). Please see the above string for further details on my query.

Thanks

Jag

• 8. Re: Creating a calculated field with measures from 2 different data sources

Hello Jagdeep,

I could not open the workbook as i have 9.3 with me, is it possible for you to upload file on the same version.

-Yogesh