11 Replies Latest reply on Dec 24, 2019 4:52 PM by lei.chen.0

# Max function to sum one field

Hi I have three different ways to cut the data for time FYP, Year or Period. The problem I'm facing is that I need to sum a measure for one period that would get me the count of people, while I need to select all periods to summarize the sales.

I was trying to do a case that when 2020 is selected then sum count of personnel for period 2. Haven't been able to get it to work though. The two fields need to be in the same sheet, but if I have to select periods 1 and 2 to get the sum of sales, this throws my count of employees off. Does anyone have any recommendations of how to work around this?

Thank you!

• ###### 1. Re: Max function to sum one field

Hello Nat,

A simple example,

Please provide a sample workbook or some sample data for further advice.

Regards

Lei

• ###### 2. Re: Max function to sum one field

I'm attaching an excel doc, one sheet has the correct count of personnel and the other sheet has the correct count of sales. I need to show the correct count of sales with the correct count of employees, the issue is selecting FYP. I need to select multiple FYPs in order to get the right count of sales, but that would give me the wrong personnel count. If I want to look year over year at increases, it makes it difficult.

• ###### 3. Re: Max function to sum one field

Hello Nat,

First, create an out join for the two sheets on [Year], [FYP] and [Field2].

Since sheet "Correct Personnel" has more [Field2] values than "Correct field 1", and sheet "Correct field 1" has more [FYP] values than "Correct Personnel", an outer join is necessary.

Then create a common year to eliminate Nulls.

And you will get the results.

A sample workbook is attached.

Regards

Lei

• ###### 4. Re: Max function to sum one field

So there's no way to do it without offloading the data and joining it back up? Like to only include the selection of FYP01 sum of personnel even when FYP01 through 04 are selected?

• ###### 5. Re: Max function to sum one field

Hello Nat,

At the first sight, I thought "Correct Personnel" is the data source for Tableau to connect, and "Correct field 1" is the calculating process indicating the logic you require.

But then I found the data in "Correct Personnel" and "Correct field 1" don't have the same data, so I thought they are different  data sources for Tableau to connect.

Regards

Lei

• ###### 6. Re: Max function to sum one field

Hi Lei,

The excel sheets are from the same source of data. They are split to show what happens when I filter for all periods vs when I filter for one. The idea would be to select all the options on a filter to get the correct sum of Field 1 but create a calculated field that would exclude all options except for one period to get the correct sum of personnel and have them displayed on the same sheet.

• ###### 7. Re: Max function to sum one field

Hello Nat,

Nat dbham wrote:

The excel sheets are from the same source of data. They are split to show what happens when I filter for all periods vs when I filter for one.

Then why are the numbers in red not matching?

By the way, is Sum [field 1] Sales?

Regards

Lei

• ###### 8. Re: Max function to sum one field

Hi Lei,

Updated so that they match for the period 01, but basically I need to sum all the periods for the sales but only get the sum of one period for the personnel. The FYP is a filter that I use so I can get the sum of sales, but you can see that it will throw the count of personnel off.

Thank you!

• ###### 9. Re: Max function to sum one field

Hello Nat,

I attached another example.

In it, Sum sales is for the whole year, and count of personnel is for the inputted period in the parameter.

Regards

Lei

• ###### 10. Re: Max function to sum one field

Thank you Lei! That worked!

• ###### 11. Re: Max function to sum one field

Happy to hear that it worked! It's a pleasure

Regards

Lei