2 Replies Latest reply on Aug 5, 2016 8:22 AM by Nathan Schneider

# Format General Ledger Data for "Normal" Reporting

For years this question has plagued me, and I'm now getting around to asking it as I haven't seen a solution yet in the forums.

Our general ledger data for our operating fund is set up as you'd expect: revenues/credits are negative numbers, expenses/debits are positive.

Everyone around the organization is used to seeing reports where revenues are positive, expenses are positive, and margin is revenue less expense:

I'm trying to get a similar report in Tableau with the least amount of calculations as possible. I've tried creating separate sheets with just the totals and then combining them on a dashboard and aligning until the sun goes down, but I don't think that is an effective method.

Below, I've created the formulas:

Revenue: if [Rev/Exp] = "Revenue" then -1*[amount] else 0 end

Expense: if [Rev/Exp] = "Expense" then [amount] else 0 end

Margin: [Revenue] - [Expense]

Then my two rows are Measure Names (rev/exp/margin) and account range. Here are my issues:

1. It shows all accounts for each measure, even if there is nothing there. So the expense accounts (2000, 2100) show 0 under revenue, and the revenue accounts (1000, 1100) show 0 under expense.
• I tried creating a formula like "sum([(Revenue|Expense)]) = 0", but this only worked with revenue or expense, not both
2. All accounts show for margin, but I would like just one line as shown above with net revenue. I get that this is probably impossible in Tableau, so I'd have to create a total on a separate sheet and align them in a dashboard. Would be nice to have collapsible row headers in Tableau though

Any idea if what I'm asking is achievable?

• ###### 1. Re: Format General Ledger Data for "Normal" Reporting

I would keep revenue and expense as one measure:    Case[Rev/Exp]   when "Revenue"  then -1*[amount]  when "Expense"  then [amount] end

Then you can possibly use a table calc to get the margin which is difference between the two by department.  If you can supply a sample workbook I can show you.

• ###### 2. Re: Format General Ledger Data for "Normal" Reporting

Thanks John. I thought a table calc gets applied to every measure along the row, so there would be a section for revenue and expense amounts and a section for revenue and expense table calc like this:

Maybe I'm thinking about it wrong. Attached is the workbook. I appreciate the time and guidance!