1 of 1 people found this helpful
You'll have to have a calc of your own, most likely.
If I understand the way you described your data, this sort of calc will work:
SUM( IF [Account Class] = "Revenue" then [Transaction Amount] END) -
SUM( IF [Account Class] = "Expense" then [Transaction Amount] END)
Sure. That makes sense. But how do you define that logic just for the Column Total?
We want the individual row values to stay as is (i.e. do not flip the sign) but have the Column Total use the calculation that you wrote down.
Do you how to apply that logic just at the total level but not the individual rows?
Mock up a sample workbook and show me what you have and describe where you want to go with it.
You're not going to get Tableau's Grand Total to do it directly. I'm looking to see what you want your sheet to look like. There is a way that you can sometimes force your own value into the grand total space, but again, it involves your own calc. I just don't want to get into the typical cycle of suggesting things and then finding out that there are other factors at play too.
2 of 2 people found this helpful
You can use Joe's suggestion of a custom calc and use some logic determine if you are in a detail or total line. The key is that in a detail line you'll only have one account class, but the total will be a mix.
IF MIN([Account Class]) == MAX([Account Class]) THEN ABS(SUM([Transaction Amount])) ELSE SUM([Transaction Amount]) END
If the MIN Account Class equals the MAX Account Class, then you are at a detail line and can show the absolute amount. Otherwise, just take the sum (negatives + positives) and show the result.
Alternately, you might also consider simply changing the display format of [Transaction Amount]. Simply right-click the field and select Defaults > Number Format. Then use a Custom format string to specify the positive and negative have the same format (without a negative indication) -- something like #,##0.00;#,##0.00 (the semi-colon separates the positive and negative formats.
Rolling up the values will still be correct (the only issue will be if the total is negative as it will still show as positive).
Hope that helps!