7 Replies Latest reply on May 23, 2016 8:33 AM by G Marc Turner

    Calculation for Grand Total

    Rajshree Das


      Hi,

       

      I am doing a calculation for a revenue per hour. I have revenue and staffedtime for each job id and for eah review month.

      If I select All, then rev per hr 11.55 however, it should give the result as 41 which is the rev per hr for job id 2. The grand total shouldn't include the nulls in the numerator and denominator for the other two job ids.

        • 1. Re: Calculation for Grand Total
          G Marc Turner

          Hi Rajshree,

          With the way you have the formula set currently it is summing revenue and hours across all jobs before making the calculation, so the hours for Job 1 and revenue for Job 3 are included in the total. The following formula for the RevperHr might work for you:

           

          {include [Job ID] : sum([Revenue])/sum(([StaffTime1  (HRS)]/3600))}

           

          This uses a level of detail calculation to get the revenue per hour by job ID. In the case where that can not be calculated, then it will be null and not included in the final calculation.

           

          Hope this helps

          -Marc

          • 2. Re: Calculation for Grand Total
            Ashish Chaudhari

            Hi Rajshree,

             

            Please refer to the screenshot below which was as per your expected output. (Month 1 is showing up 41.4 and similarly for others.)

             

             

            I observed that the workbook that you have attached is the older version of tableau. I am using tableau 9.3. I will attach the workbook in my next post since community has some problem today regarding uploading the files.

             

            I have Created following calculations.

             

             

            1. Calc_Null_Filter (put this in filter shelf and select False which will filter null records)

            [RevperHr]=0

             

            2. LOD_RevPerHr

            { FIXED [Review Month],[Job ID]: [RevperHr]}

             

            Please arrange things as per the screenshot you will get the output.

             

            Thanks and Regards,

            Ashish Chaudhari

            • 3. Re: Calculation for Grand Total
              Ashish Chaudhari

              Workbook upload attempt 1

              • 4. Re: Calculation for Grand Total
                Rajshree Das

                Hi Mr. Turner,

                 

                Thank you for the response. However, I am using a lower version 8.3 and this formula doesnt seem to work. Giving me a bad character error.

                Any way to work this out in the lower desktop version.

                 

                Thank you..Rajshree

                • 5. Re: Calculation for Grand Total
                  G Marc Turner

                  The attached workbook might have a solution that will work for you. I created a calculated field that checks to see if either Revenue or StaffTime is null. By adding that to filters, it should filter out the records that are creating the problem. I'm not sure if this solution will work in all cases, but it appears to work with the data you have provided and does not use the Level of Detail calculation.

                  Hope this helps

                  -Marc

                  • 6. Re: Calculation for Grand Total
                    Rajshree Das

                    Hi Marc,

                     

                    I am unable to locate the attachment. Hope I am not missing on anything.

                     

                    Regards,

                    Rajshree

                    • 7. Re: Calculation for Grand Total
                      G Marc Turner

                      Hi Rajshree,

                      The attachment is showing on my end. In order to view the attachment, you will need to go to the actual thread rather than viewing it in your inbox. Here is a link back to the thread:

                       

                      Calculation for Grand Total

                       

                      If you still can't see it when viewing the thread, please let me know and I will try to repost it.

                       

                      -Marc