4 Replies Latest reply on Aug 19, 2012 7:08 PM by yao.fu

    calculate rows as a new row

      Recently I need to deal with this kind of situation, as the picture shows:


      I want to fill the blank row of CR with the monthly calculation result of BR minus OO or BR plus OO. But the layout of the sheet shall not be changed.

      Is that possible to achieve in tableau?  Great appreciation for your replies and solutions!

        • 1. Re: calculate rows as a new row
          Mark Holtz

          Pretty sure you would need to modify the underlying data...


          For example, in your underlying data, you only have 1 record with [Channel]="22 Customer Research."

          If you think about that in terms of the pivot table you created in Excel and in Tableau, there is "No Data" for the intersection of [Channel] and [Monthly data] except for that single value for 012/2011 in which the value is blank.  If you replace it with 0 and refresh your pivot table, you'll see what I mean.


          That record is the only reason the "22 Customer Research" even appears in the table. Similarly, there are 9 records with "20 Brand."  As far as I know, it is not possible to make pivoted calculations when there are not values underlying the intersection point--in either Tableau or Excel.


          You could maybe get around this by adding a record for every Channel & Monthly data possible in the Excel source and (including "20 Brand", "22 Customer Research" & "24 Other Offline Unmapped") then probably get to what you're after with Tableau... but it might actually be easier to do in Excel with an IF statement and a SUMIF, which works like a pivot table field in a regular table.

          1 of 1 people found this helpful
          • 2. Re: calculate rows as a new row

            Mark, thanks for your reply. But I think there are some points that I might not make clear.


            First, I make these lines with blank value or single value appear intentionally. Because I want to use the dashboard as a template that people can download a excel copy with it.


            And now I want to replace the blank row with the calculation result of two other rows. And these two rows don't have blank value. So we don't need to make pivoted calculations when there are not values underlying the intersection point--in.


            Finally I know that Excel is more convenient to do this kind of calculation, but this is a must that I shall use the Tableau dashboard to achieve that goal. So I am quite stuck here with this problem.


            Looking forward for your further suggestions,

            1 of 1 people found this helpful
            • 3. Re: calculate rows as a new row
              Mark Holtz

              I went ahead and added the needed missing data to your Excel file.

              Then, I created a couple of calculated fields to "keep" the 10 Other and 21 Buyer data while throwing everything else. (You specified that those are the fields that impact the specific rows that are based on aggregations).


              Then, I created an alternate (adj) version of your amount measure using an IF statement to isolate the dimensions you said need to be treated differently. It uses TOTAL(SUM(x)) aggregations, which will pull in data for all rows for a dimension, not just for the row on which the calculated field is made.


              The last crucial step is that you must ensure the Amount (adj) field always re-calculates as TABLE DOWN, as it depends on the values in other "positions" in the grid to calculate.


              Not a terribly nimble solution, but I got it to work.

              • 4. Re: calculate rows as a new row

                Great Job!  Altough it is a little bit complex, it does solve my problem! I'll try to understand the method and principle.

                Thanks a lot for your time!