2 Replies Latest reply on Feb 22, 2016 5:36 AM by Eric Feiss

    Using a Grand Total in a Calculated Field

    Eric Feiss

      I'm new to Tableau and started working on what I thought would be a simple table only to get stumped. Below is my attempt at explaining what my objectives and the roadblock I'm hitting. I appreciate any insight or advice this community can offer.

       

      I am trying to create a table that will provide an understanding of remaining fee to be billed per projects in our office. This requires that I know the available fee for each project and how much we plan to bill per month per project. I would like to subtract the grand total of the billings/month from the $ NET FEE. We have projects that extend multiple months and years and we project our monthly billings. The goal of this exercise would be to understand how much fee would remain after a certain time period.

       

      For example: in the table below, Project 1 has a total fee of $617,589. We will bill $758 in Feb, $12,232 in Mar, and $6,116 in Apr for a total of $19,107 over the next three months. I'd like to see how much fee will remain after the three months. I would also like to change the projection period, at times we may want to look beyond the next 12 months or 2 years. I would also like to total all the remaining fees by project to see what the offices planned billings will be.

       

      Seems like a super simple calculation but I can't seem to get it to work. I've attached a packaged workbook for reference.

       

       

      Any help would be greatly appreciated!

       

      Eric

        • 1. Re: Using a Grand Total in a Calculated Field
          Alec Barrett

          You can accomplish this using Level of Detail calculations. Here's how I'd set it up, but if you're set on having the totals to the right, you can make them separate worksheets and put them side-by-side on a dashboard.

          Your new calculated fields:

          Sum: {FIXED [p NAME]: SUM([$])}

          Diff: [$ NET FEE] - [Sum]

           

          One tricky thing about Level of Detail calculations is that the "FIXED" constructor is evaluated before ordinary dimension filters. To ensure that the dates you want are evaluated before the Project-level sum is calculated, make Year(Month), TL, and MGR context filters (right click and select "Add to Context").

          1 of 1 people found this helpful
          • 2. Re: Using a Grand Total in a Calculated Field
            Eric Feiss

            Alec,

             

            Thank you so much for taking a look at this. Works beautifully!

             

            Eric