1 Reply Latest reply on Sep 27, 2012 12:29 PM by Mark Holtz

    Capacity line on a chart (sum distinct??)

    Brad Wilkenson

      I have a large dataset that includes a bunch of data about a department forecasts at multiple sites.  Each of these departments has a different "Capacity".  I want to be able to show a straight horizontal line that represents the total capacity for each of the departments chosen in the filter.  I have tried to do this using data blending, as well as trying some type of calculation. 

       

      My issue with the data blending option is I do not want the departments to be plotted individually.  I want them to be aggregated together.

       

      My problem with the calculation, is that there can a be varying number of records for each department based on some other more granular data that is in the dataset, and I cant figure out a good way to get the true overall capacity (i keep ending up with some form of a weighted average).

       

      It seems like I'm trying to do something like a SumDistinct (which I dont think exists). 

       

      I've attached a packaged workbook that has some examples, and more description of my problem.  Any help would be greatly appreciated.

       

      Thanks.

        • 1. Re: Capacity line on a chart (sum distinct??)
          Mark Holtz

          Think you are after a WINDOW_SUM. Attached is your workbook with what I'm talking about.

          WINDOW_ calcs are what's known as table calculations. They allow you to show a "roll-up" of both the capacity and also if you wish, the forecasts. (From what you said, it didn't seem like you were asking for that.)

           

          Table calculations depend on the way the view is constructed to calculate. It's like having a way to get the total roll-up in each "pane", "cell" or other pattern when it may not be not available in the underlying data. (imo, the best way to start is to look at your data in a cross tab to try to figure out what you want it to show.)

           

          I created a calculated field (in both of your data sources) with a WINDOW_SUM of the measures you were using. Then, after dragging the field onto your view, you can choose the addressing and partitioning rules, (right click the measure pill, choose "Edit Table Calculation"  and then select "Advanced" in the Compute Using: option.

           

          The simple rule I ran across was to "ignore" fields in the view that remain on the left panel (Available fields:) and move everything that matters for the aggregation to the right panel (Compute Using:).

           

          I apologize, but I am just not very good at explaining what's going on behind the scenes. Table calculations are among the most challenging (and powerful) tools Tableau offers.

           

          Jonathan Drummey put together a wonderful blog resource all about this topic:

          http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/

           

          If you want the formal Tableau explanation, this is a good place to start

          http://www.tableausoftware.com/sites/default/files/pages/table_calcs_in_tableau_6.pdf

           

          Hope this is helpful!