3 Replies Latest reply on Apr 13, 2012 3:50 PM by Jonathan Drummey

    Binning an aggregated measure

    Rick Wells

      I have a problem in trying to use bins for an aggregated field, Total Margin%. How can I work around this issue?

      Attached is a sample data set of one contract with many items. My database has hundreds of contracts with up to 40k items per contract.

      I have seen using IF to create bin 'sets'. This will not work as there will be a wide range of Total Margin%, depending upon the contract data set.

      Also, I am using v6 not v7.Does v7 solve this issue?

      Any help would be greatly appreciated.

        • 1. Re: Binning an aggregated measure
          Jonathan Drummey

          Hi Rick,

           

          Based on your sample data, I'm not understanding why you can't use the existing bin functionality in Tableau, the bin functionality works on measures and aggregate measures and as far as Tableau is concerned the spreadsheet you provide just has a bunch of measures in it. It won't work on aggregate measures that are made up of table calculations, however there are a couple of workarounds. You don't say what you'd like to do with the bins, I'm assuming here that bins are really what you need.

           

          IF statements are one solution. Another is to create a calculated field that returns a set of values based on the data, then make that returned field discrete. For example, you could do ROUND(LOG([Total Margin%]),1) or some other calculation. Then you can make that calculation discrete (right-click on the measure and choose "Convert to Discrete", and use that as a blue pill in your calculations. I've done that in the attached workbook.

           

          Jonathan

          • 2. Re: Binning an aggregated measure
            Rick Wells

            Jonathan,

            I appreciate the quick response. I failed to mention that in the data set that col. H-K were calculated values and that I needed the Total Margin% to be extended overall margin% after extending with the unique qty field. As you know, that calc in tableau is an aggregation and no bin option is available.I was trying to get the attached type of histogram. I hope this makes sense.

             

            Regards,

            Rick

            • 3. Re: Binning an aggregated measure
              Jonathan Drummey

              Hi Rick,

               

              Without seeing your workbook, I don't know what you mean by "...I needed the Total Margin% to be extended overall margin% after extending with the unique qty field." However, I have attached a workbook where I demonstrate how to create several different histogram-like views, depending on whether you are starting with a measure or a table calculation. There are drawbacks either way, one method creates discrete values for the histogram columns and is labeled like your graph, but doesn't show the intervening gaps for empty values like a true histogram would, the other way uses a continuous measure that shows the intervening gaps but is not able to properly label the axis.

               

              I'm not sure which view will work for you, if any, let me know if this is helpful!

               

              Jonathan