4 Replies Latest reply on May 21, 2012 3:27 PM by Craig Mullins

# i need to create bins of total sales (not based on each transaction size) by customer

the calculated field i created segregates by each transaction size not by the total sales for the period for each customer. how do i sum the sales by customer first and then have the bin group by total sales for the year or month.

my file has 7,000,000+ rows, so instead i have attached a very simple example data file.

i am sure there is a simple step i am missing.

thanks

• ###### 1. Re: i need to create bins of total sales (not based on each transaction size) by customer

The problem here is that Tableau doesn't allow creating bins from calculated fields, and you'll need a calculated field to use sum of sales by customer instead of individual sales.

The simplest solution is to per-aggregate the data in the connection, via custom SQL for example.

Another way to approach it is to leave data as is, but instead of built-in Tableau binning function use calculated field, i.e.

`IF SUM([Sales]) < 1000 THEN '0-1K' `

`ELSEIF SUM([Sales]) < 2000 THEN '1-2K' `

`etc... `

• ###### 2. Re: i need to create bins of total sales (not based on each transaction size) by customer

the new calculated field is converted to a measure and i cannot drag it to become a dimension??

how can i convert these now correct bins to a dimension?

• ###### 3. Re: i need to create bins of total sales (not based on each transaction size) by customer

Hi Craig,

These correct bins should still be acting as dimensions, they're just not in the dimensions portion of the data box because there is an aggregation on Sales.

-Tracy

• ###### 4. Re: i need to create bins of total sales (not based on each transaction size) by customer

tracy,

there are no subtotals.

when i try to group donors into bins - it seems i can only display all the individual names segregated into the bins.

how can i show just the aggregate amounts per bin, average per bin and unique counts per bin(i have these measures already prepared) using this calculated field?

thanks for your help with what i know must be a simple matter, i just can't see it.

surely such a calculated field used to create bins will provide for each bin, the aggregate/subtotals ber bin for amounts derived from the individual transactions??

Craig