8 Replies Latest reply on Jan 31, 2016 6:42 PM by pooja.gandhi

    Adding within a dimension

    Samantha Cutter

      Hi,

       

      I have the following chart.  I need another account that calculates the difference (essentially a plug) between the yellow highlighted accounts and EBITDA.  The chart should be the yellow highlighted accounts plus an additional line for "Other" all summed to equal EBITDA.  Is there a way to accomplish this?

       

        • 1. Re: Adding within a dimension
          Adam Crahen

          Hi Samantha-

           

          What kind of data source are you connecting to?

          • 2. Re: Adding within a dimension
            Samantha Cutter

            An Excel file

            • 3. Re: Adding within a dimension
              pooja.gandhi

              I am kind of confused by your question. First you say you need a calculation that takes the difference between the highlighted values and EBITDA and then you say you need an additional line for 'other' all summed up to equal EBITDA? I am not sure I understand what you are looking for. If you are however, looking to take the difference you can use a table calc to do so. Something along the lines:

               

              EBITDA Calc: window_max(IF ATTR([Account (Group)]) = 'EBITDA' THEN sum([Value]) END)

               

              This will fill all rows with the EBITDA value and then you can simply take the value field and subtract from the calc. If you need something different, it might be worth attaching some sample data for us to play with. Also noting which version of Tableau you use would be helpful because depending on the version the solution might be easier or harder.

               

              Pooja.

              1 of 1 people found this helpful
              • 4. Re: Adding within a dimension
                Adam Crahen

                I think what you are saying is EBITDA is the total and the yellow lines are a subset of the total, but you are missing one line.  The easiest thing to do is to just add that other line to your excel sheet and insert the calc to subtract the sum of detail from EBITDA directly in Excel.

                 

                If for some reason that is not possible, you can add a new sheet to the same excel workbook padding the data.  I attached a quick sample of this.

                 

                Then in Tableau, do a Left Join on your padded dimension with you actual data.

                2016-01-31_00-57-30.png

                 

                Then you can use the padded dimension in your view.

                2016-01-31_01-02-04.png

                The formula below generates the Other amount when the sort keeps Other right above EBITDA.

                 

                Calc Amount

                IF ATTR([Account (group)]) = "Other"

                THEN WINDOW_MAX(sum([Amount]))-LOOKUP(RUNNING_SUM(SUM([Amount])),-1)

                ELSE SUM([Amount])

                END

                 

                9.2.2 workbook and quick excel sample attached.

                3 of 3 people found this helpful
                • 5. Re: Adding within a dimension
                  Samantha Cutter

                  Thanks, Adam!

                   

                  Your solution worked.  I appreciate it!

                  • 6. Re: Adding within a dimension
                    Adam Crahen

                    You're welcome.  Can you mark any of the posts you found to be helpful/correct?  I think you marked the wrong one correct.

                     

                    Thanks.

                    • 7. Re: Adding within a dimension
                      pooja.gandhi

                      Adam - marked your answer correct!

                       

                      Pooja.

                      1 of 1 people found this helpful