1 Reply Latest reply on Jul 29, 2016 10:31 AM by Andrew Watson

# Calculate banding at an aggregate level

I want to sum the sales for each individual and put them into sales bands.  I also want to see the counts of sales individuals per band.

In my end result, I don't want to see the individual names; I just want the Sum of Sales & Counts that are reflected in the "End Result-Band" section below:

I can get the view below, but if I keep the bands as a dimension, it's not summing up the total sales for that individual & putting in the right
band.

If I aggregate the sum of sales & it's a measure, I can't get the totals by bands!

I am attaching the workbook.  Thanks in advance for your help!

• ###### 1. Re: Calculate banding at an aggregate level

If you're using Tableau 9+ this is straightforward as LOD calculations make this type of thing quite simple. I recommend this page: Top 15 LOD Expressions | Tableau Software

Create a calculated field, IndividualSales to lock in the sales per individual: { FIXED [Individual]:SUM([Sales])}

Use that field to create your bands (I edited you banding calculation):

If [IndividualSales] <= 99999 then '\$0- \$99,999'

Elseif [IndividualSales] > 99999 and [IndividualSales] <= 249999 then '\$100,000-\$249,999'

Elseif [IndividualSales] > 249999 and [IndividualSales] <= 499999 then '\$250,000-\$499,999'

Elseif [IndividualSales] > 499999 and [IndividualSales] <= 999999 then '\$500,000-\$999,999'

Elseif [IndividualSales] > 999999 and [IndividualSales] <= 4999999 then '\$1,000,000-\$4,999,999'

Elseif [IndividualSales] > 4999999 then '\$5,000,000+'

ELSE Null

End

Effectively that's it, now done, just a case of using your new fields. I created the below: