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

    Format General Ledger Data for "Normal" Reporting

    Nathan Schneider

      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?