4 Replies Latest reply on Jan 23, 2017 7:25 AM by Joe Oppelt

# How to use the coloum value of a particular row as a calculation for all the rows? I need to have the coloumn Expense1_New such that it is the sum of Expense1 and random percentage of Expense1 value of x, Similarly for Expense2_New also.Please help in creating the calculated field for Expense1_New and Expense2_New

• ###### 1. Re: How to use the coloum value of a particular row as a calculation for all the rows?

To get the sum of the Expense1 column do:

WINDOW_SUM(SUM(Expense1))

That is a table calc.  Put it on your detail shelf and edit the table calc so that it evaluates TABLE(down).

(Special note:  I can't guarantee that TABLE(down) is actually the correct setting.  Based on what your screen shot shows, it probably is, but there may be specifics in your application that requires something else.  If TABLE(down) doesn't do it for you, and you need help figuring out the proper setting, you will have to upload a packaged workbook and your tableau version so that someone can look at the specifics.)

Then you can create another calc that does this:

SUM([Expense1]) +  (SUM([random Percentage)] * [the table calc you created above])

That will give you Expense1New.

You'll follow the same steps for Expense2New.

• ###### 2. Re: How to use the coloum value of a particular row as a calculation for all the rows?

Hi Joe,

The only problem i am facing is that I cant create a field which is constant( ie the expense1 value of X) for all the rows irrespective of the 'Name'.Can u please help on that?

• ###### 3. Re: How to use the coloum value of a particular row as a calculation for all the rows?

A LOD calculation could do that. Something along the lines of

{FIXED: SUM(IF [Name] = 'X' THEN [Expense 1] END)}

1 of 1 people found this helpful
• ###### 4. Re: How to use the coloum value of a particular row as a calculation for all the rows?

I misunderstood your question and screen shot.  I took "X" to be a total on the sheet.

You can do either what Andrew suggested with LOD, or, you can create a calc that looks like this:

IF [NAME] = "X" then [Expense 1] END

then use SUM([that calc]) wherever you need to get that figure.  (Note:  "X" will have to be on the sheet to use it that way.  But "X" does NOT have to be on the sheet if you use the LOD calc.)

Will "X" always be constant?  Or will you need to compare to different [NAME] values at times?  If you need to compare to different [NAME]s, then give the user a parameter to specify what "X" should be, and use that instead.  It will work in the LOD calc as well.