7 Replies Latest reply on Jul 23, 2013 10:06 AM by Eric Machak

    Table row or column subtotal from calculated rounded down value?

    Eric Machak

      Hi,

      My issue is that when using a calculated field with rounding Tableau v8 seems to be performing the subtotal calculation, and then rounding the result, as opposed to totaling up a series of rounded values. This happens whether I am using Round() or Int() (I want to round down).

       

      For example, in table below tableau is returning 17 as the subtotal, whereas I would like to see 14.

       

      RawRound
      2.72
      3.83
      4.74
      5.95
      17.114
        • 1. Re: Table row or column subtotal from calculated rounded down value?
          Jim Wahl

          The best solution will depend on your worksheet layout, and you might want to post a sample packaged workbook that shows the view layout and goal.

           

          BUT, you might be able to get away with something like

          Value (Rounded) =

               INT(SUM(Value))

           

          Assuming you have one value per row in your view, SUM(Value) == Value, but on the subtotal row, Tableau will remove the right-most dimension and it'll sum all of the values and then round it using INT().

           

          Jim

          • 2. Re: Re: Table row or column subtotal from calculated rounded down value?
            Eric Machak

            Jim,

            Thanks for your reply. I tried your solution above but it didn't work as the value Im trying to round is already aggregated. I have attached some mock data and and an associated tableau workbook that has more detail on what I am trying to accomplish.

             

            On the Import tab of the excel workbook is a list of managers, along with function, level and "SoC" which represents their "span of control" or the number of employees each manager has reporting to them.

             

            The Calculations tab walks through the analysis I am trying to replicate in Tableau.

            First, I assume a target SoC of 4, meaning each manager should ideally have no fewer than 4 direct reports.

            Next, I find the "ideal" number of managers in each combination of function and level. This is equal to the total number of employees (Sum of SoC) divided by the target SoC. So if there at 8 employees in the SM/HR pool, the "ideal" number of supervisors is 2.

            Finally, I subtract the ideal number of supervisors from the actual number of supervisors (Count of Soc) to calculate the "opportunity". Now because we cannot deal in terms of fractions of people, I need to round this opportunity down. This is shown as the Rounded Opportunity in excel.

            The problem in tableau is that when I try to add column and row totals to the rounded opportunity measure, it first adds up the raw opportunity, then rounds the result.

             

            For example, in my sample data, the total opportunity in the Finance function should be 1+0=1 manager, however tableau reports 1.25+.75 = 2 managers.

             

            I would really like to be able to do this calculation in Tableau, as I have the target SoC set up as user editable parameters in my workbook.

             

            Any additional help would be much appreciated.

             

            Thanks,

            • 3. Re: Re: Re: Table row or column subtotal from calculated rounded down value?
              Jim Wahl

              Hi Eric,

               

              Thanks for the description and workbook. Just a note for the future, you can save the extract file (tde) with the workbok fille (twb) using a packaged workbook (twbx).

               

              Grand totals are a deceptively tricky area of Tableau. The key realization is that grand total is not summing the columns, but it's removing the dimensions on the columns shelf and then applying the aggregation specified for the measure.

               

              For example, in your current view Raw Opportunity and Rounded Opportunity are calculated for each Level (VP, SM). But for the grand total, the level is removed and the aggregation is done over all values, before the INT() function is applied. You can duplicate the behavior of grand total by removing Level from the worksheet.

               

              The are a couple of ways around this problem. The easiest in this case may be to increase the level of detail in the view and the use WINDOW_SUMs to aggregate the measures.

               

              This is somewhat easier to see than to explain:

              1. First increase the level of detail by duplicating Level (right-click > duplicate) and move this new field  Level (copy) to the level of detail shelf. Now grand total removes the Level dimension, but Level (copy) is still present and, therefore, you'll see two values for each measure in the grand total, one for each level in the Level (copy) dimension. We want to sum these values to get the grand total.
              2. Next, right-click on Rounded Sum and select create calculated field. For the formula just wrap the measure in a WINDOW_SUM() function: Rounded Opportunity (WS)WINDOW_SUM([Rounded Opportunity])
              3. Add this new field to the view, right-click on the measure pill and select compute using > Level (copy). Compute using is the "addressing field" that the table calc is calculated over. Now you should still have two copies (since there are two dimensions), but the value should be correct since it's computed over all marks in the dimension.
              4. To get rid of the second mark, wrap the Rounded Opportunity (WS) formula in an IF FIRST() == 0 THEN  .. END. You'll see this notation a lot with table calcs, and it just means only return a value for the first mark.
              5. Eliminate the white space by selecting Analysis > Wrapped Marks > Off from the top menu bar.
              6. I did't do this, but you can right-click on each measure name and select edit alias to remove the (WS) ... suffix.

               

              The downside to this approach is that you'll need to create WINDOW_xxx version of all your measures. The upside is that you can change how each measure is aggregated (the _xxx) in the grand total. Another benefit is that you're still using Tableau's grand total functionality, which provides some nice formatting options for the grand total row.

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Re: Re: Re: Table row or column subtotal from calculated rounded down value?
                Eric Machak

                Jim,

                Thanks for your reply, it was very helpful. I am able to get this working correctly when totaling up the columns (or rows, if I repeat this procedure for function instead of of level). However, is there a way I can make this work for both rows and columns, so that the grand total for all cells will be accurate? I think I would need to window_sum() to run for both level and function, but I can't figure out how to make that work. Note, in the mock data I created this is not an apparent issue, but I attached a screen shot that shows the problem I am facing.

                 

                Thanks,

                • 5. Re: Re: Re: Re: Table row or column subtotal from calculated rounded down value?
                  Jim Wahl
                  I would need to window_sum() to run for both level and function, but I can't figure out how to make that work.

                  I think you're on the right track. After adding a duplicate dimension for Function to the level of detail shelf, you'll want the compute using to be set to both Function (copy) and Level (copy). You can do this by clicking on Rounded Opportunity (WS) table calc pill > Edit Table Calculation > Compute Using > Advanced. And then move both of these "...(copy)" dimensions from the Partitioning side to the Addressing side.

                  • 6. Re: Re: Re: Re: Table row or column subtotal from calculated rounded down value?
                    Jim Wahl

                    And you may want to vote for this idea to allow grand totals / subtotals on marks instead of removing a dimension:

                    http://community.tableau.com/ideas/1232

                    • 7. Re: Re: Re: Re: Table row or column subtotal from calculated rounded down value?
                      Eric Machak

                      YES! This was the solution I was looking for, thanks again for your help.