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

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

Hi Samantha-

What kind of data source are you connecting to?

• ###### 2. Re: Adding within a dimension

An Excel file

• ###### 3. Re: Adding within a dimension

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

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.

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

Your solution worked.  I appreciate it!

• ###### 6. Re: Adding within a dimension

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.