7 Replies Latest reply on Nov 5, 2017 3:09 PM by Alexander Mou

Sum sales when postal code is the same

Hi guys,

I just encountered one question. The question asks us to sum the sales for each individual postal code. For example, when the postal code is 32216 when need to sum all the sales with a postal code of this number.

I was trying to use sum if but it says can not have one aggregated and one non-aggregated function. Anyone who knows how to do it?

Thank you!

• 1. Re: Sum sales when postal code is the same

You should be able to drag [ZIP Code] to Rows, and [Sales] to Columns (where it will automatically become SUM([Sales]). This kind of question doesn't need a special calculation.

For instance, with the Superstore data set:

• 2. Re: Sum sales when postal code is the same

try

sum(if Post_Code=32216 then  end)

or

sum(if Post_Code='32216' then  end)

On Sun, Nov 5, 2017 at 10:11 AM, Nan Chen <tableaucommunity@tableau.com>

• 3. Re: Sum sales when postal code is the same

Hi Branden

I think we were actually just using the superstore data. I'll do it and let you know if that is my desired results.

Thank you so much!

Sent from my iPhone

• 4. Re: Sum sales when postal code is the same

Hi Alexander,

Do you know what I can do if there are a lot of zip codes. The question asks us to come up a zip code with the highest total sales for each state.

Thanks.

Sent from my iPhone

• 5. Re: Sum sales when postal code is the same

Hi Nan,

Sort by sum(sales) will give you the highest total of sales, but not across each state.

For the correct method of doing this calculation - watch this video Filtering for Top Across Panes

HTH

Peter

• 6. Re: Sum sales when postal code is the same

Do it this way:

1.drag Sate and Zip Code to the rows

2.create a calc field [Neg Sales]: -sum(sales) and make it discrete (in blue)

3.drag it to the rows, between State and Zip Code.

4.drag Sum(Sales) to the columns.

5.hide the headers of [Neg Sales]

Then you are done.

Note that Neg Sales is purely for ordering.

• 7. Re: Sum sales when postal code is the same

Another way to do it is:

put this into a filter

Rank_Unique(Sum([Sales])=1

set the filter to compute along Zip Code.

This will give you one zip code per state with the highest sales in that state.

1 of 1 people found this helpful