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

    Calculate banding at an aggregate level

    Ann Klein

      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
          Andrew Watson

          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: