
1. Re: "cannot mix aggregate and nonaggregate" error.
Robert Breen Mar 14, 2019 12:41 PM (in response to Jenifer Lee)1 of 1 people found this helpfulHello Jenifer,
Since you have an aggregation (SUM) in the metric calculation, you will also need to aggregate the operations calculation to be able to use them in the same calculated field.
solution 1:
update operations to be: sum([Air Carrier]+[Air Taxi])
solution 2:
update your final calculation to be: [METRIC]*1000000/sum([Operations])
Hope this helps!
Robert 
2. Re: "cannot mix aggregate and nonaggregate" error.
Ewald Hofman Mar 14, 2019 12:45 PM (in response to Jenifer Lee)1 of 1 people found this helpfulThink of it in this way. The data is presented to Tableau as a table. [Air Carrier] And [Air Taxi] are two columns in that table. The Operations calc is thus another column in the data source behind the viz.
The METRIC however is an aggregate for whatever dimensions you have added to the Columns and Rows shelf, and is not part of the data source behind the viz.
Because they are different (aggregated for METRIC and nonaggegated for operations), you can't combine them. You will need to make your Operations also aggregated. There are multiple ways
1. Update your Operations metric to SUM([Air Carrier] + [Air Taxi]) to create a similar aggregation. Now you can use these two calculated fields to calculate your safety metric.
2. Change your safety metric to [METRIC]*1000000/SUM([Operations])

3. Re: "cannot mix aggregate and nonaggregate" error.
Jim Dehner Mar 14, 2019 12:45 PM (in response to Jenifer Lee)1 of 1 people found this helpfulHi
first welcome to Tableau and the forum
aggregates and nonaggregates  I am going to assume that you come to tableau with experience with spreadsheet calculations
database oriented systems are very different form that  the dimensions are used to categorized data and the measures are the actual values
when you bring a dimension/measure to the canvas the measure is aggregated  it like taking a whole column of spreadsheet data and aggregating it 
so far no problem  BUT then every dimension or measure in the viz also needs to be aggregated at the same level 
Pretty easy to understand with numbers but you also have to aggregate dimensions including strings (text) and dates
Attr() for attribute is an aggregate function that can be used  but take care it doesn't compute and can result in * meaning that the aggregation does not contain unique values
min(), max() are also aggregates  dates are sequential and strings ar alphanumeric sequence so in your case you probably need to attr([Air Carrier])+attr([Air Taxi])
and sum(metric) and sum(operations) but that is only a guess without seeing your data and twbx workbook
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

4. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 14, 2019 1:09 PM (in response to Robert Breen)What if I do not want the sum of the whole column added together but for each row specifically.
When I try either solution 1 or solution 2 it is not getting the correct numbers because I'm guessing that it is adding the total of that whole column 
for example, if i add
solution 2:
update your final calculation to be: [METRIC]*1000000/sum([Operations])
it is dividing by the total number of operations not by the operations for that month.

5. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 14, 2019 1:09 PM (in response to Jenifer Lee)By the way, thank you very much for the fast response and help!!!!!

6. Re: "cannot mix aggregate and nonaggregate" error.
Jim Dehner Mar 14, 2019 1:08 PM (in response to Jenifer Lee)Jennifer
Thanks
the sum() used on measures is also filtered by the other dimensions and measures on the sheet 
think of it this way  you want to find the orders associated with a customer and order number  so you put the customer and the order in the vis and sum the sales because there were several lines on the order 
you are still getting only the sales on that order but to get them there were 2 records in the data that were added together
had there been only one record you would still use sum() but there would be only one record to total
Jim

7. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 14, 2019 1:19 PM (in response to Ewald Hofman)What if I do not want the sum of the whole column added together but for each row specifically.
When I try either solution 1 or solution 2 it is not getting the correct numbers because I'm guessing that it is adding the total of that whole column 
for example, if i add
solution 2:
update your final calculation to be: [METRIC]*1000000/sum([Operations])
it is dividing by the total number of operations not by the operations for that month.

8. Re: "cannot mix aggregate and nonaggregate" error.
Ewald Hofman Mar 14, 2019 1:21 PM (in response to Jenifer Lee)If you want that, you will need LOD expressions, such as
Overview: Level of Detail Expressions  Tableau
{FIXED DATETRUNC('month', MyDate) : [METRIC]*1000000/sum([Operations]) }

9. Re: "cannot mix aggregate and nonaggregate" error.
Jim Dehner Mar 14, 2019 1:23 PM (in response to Jenifer Lee)Jennifer  it is difficult to see what is going on with out you book  please post it when with some dummy data 
could be a number of thinks  is all the data from a single source/  houw is the date incorporated in the viz 
you could end up with an LOD like {fixed mont(date),year(date) :sum(operations)} but that would depend on the data, the data structure and other calculations in the viz
Jim

10. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 15, 2019 7:12 AM (in response to Jim Dehner)Hi,
I am sorry for all the screenshots. I am not in the office and had someone send me screenshots and this is the best I can do at the moment.
1. Showing the Data Source and the columns.
2. This is the graph we want with the New normalized metric (METRIC 2) , currently using the METRIC calculated by our company.
3. Table showing the correct number of operations and the numbers for "METRIC"
4. The "CANNOT MIX AGGREGATE AND NONAGGREGATE" ERROR when in the METRIC2 pill5. METRIC pill formula
6. Operation pill formula
7. When changing the Operation pill's formula to SUM([Air Carrier] + [Air Taxi]) the numbers change.
So in the data set there are many events with the same month and year. I believe it is adding all the number of operations of each data entry for the same month and year as shown in the data.
I would like for the unique number of operations for that month and year.
If these screenshots do not help I can post the dummy data next time i'm in the office.
If you need other screenshots I can ask for her to take some so please feel free.
Thank you all again for all the help for the noob tableauer

11. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 15, 2019 7:49 AM (in response to Ewald Hofman)please see comment below!

12. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 15, 2019 9:40 AM (in response to Jim Dehner)I have attached a sample workbook

13. Re: "cannot mix aggregate and nonaggregate" error.
Ewald Hofman Mar 15, 2019 9:40 AM (in response to Jenifer Lee)1 of 1 people found this helpfulIn your screenshot you use the ATTR(Operations) to put on your Viz. Have you tried to update your formula to
[Metric] * 1000000 / ATTR([Operations])

14. Re: "cannot mix aggregate and nonaggregate" error.
Jenifer Lee Mar 15, 2019 9:48 AM (in response to Ewald Hofman)Oh wow!!! It worked!! Thank you so much!!!!