3 Replies Latest reply on Aug 9, 2016 7:57 AM by Elizabeth Kreitler

    Calculated Field for Adding Budget

    Jaspreet Narang

      Hi Tableau community,

      I am going to explain my question with an example - I made a dashboard that looks like the one below

      I created a parameter called "Test Parameter" which shows Programs when the option selected is 'PR' and shows Master projects when the option selected is 'MP'. Independent Projects don't belong to any category therefore it's shown in both options.

       

      You can drill down within a Program or a Master Project by simply clicking on it. For this example I clicked on Systems and as you can see on the right side all the projects that belong to the program called "Systems' only get displayed.

       

       

      The image below shows the budget and the money spent on the program(When you hover over the blue bar it displays the money spent). It's basically the sum of all the projects that belong to the program.

      The issue that i am facing here is that the budget and actuals(money spent) for a master project is the sum of all the projects that belong to the master project PLUS the budget of the master project itself. i am not sure how to incorporate that in my view and I am having difficulty in coming up with a calculated field for that.

       

      Please help me with this issue.

       

      I have attached the workbook(Version 9.0) and the source files also.

       

      Best Regards,

       

      Jesse

        • 1. Re: Calculated Field for Adding Budget
          Elizabeth Kreitler

          Hi Jaspreet-- not sure if this is what you're looking for but here's my stab at it: First i created a project type field, this stores whether the project type is bigdata [which is the only value for master project, right?] or program, or independent.

           

          Then I'd just create a calculated field that assigns a different value for budget if the project type is BigData:

           

          I just don't see from your data/description above, where this additional cost for the master project itself is stored.

          1 of 1 people found this helpful
          • 2. Re: Calculated Field for Adding Budget
            Jaspreet Narang

            Hi Elizabeth,

             

            If you look at the data source the project type for a Master Project is "Master"

             

            In the pic below I have underlined the projects along with the budget costs that belong to Big Data and circled the master project called "Big Data" itself. The value for the budget comes from the Budget Costs column and the value for the money spent comes from the "Actual Cost" column. So the value for the master project called big data will be = $200,000 + $200,000 + $200,000 + $200,000 + $50,000 = 850,000

             

            Thanks,

             

            Jesse

            • 3. Re: Calculated Field for Adding Budget
              Elizabeth Kreitler

              Okay-- this may not be a long term solution but here's a way to at least get to the value you're wanting, maybe put you on the right track. I added a sheet at the end filtered for projects that contain "BigData or Big Data" and added in the total. Assuming in your second screenshot below you also meant to include project ID BigData5, then this total value is the value you're looking for.

               

              I then recreated the barchart on the next tab using the grand total as the budget bar. Now you can just update your dashboard to fitler to that worksheet when the user clicks on Big Data.

              1 of 1 people found this helpful