4 Replies Latest reply on Jun 6, 2016 10:56 PM by Raja Saab

    Calculation Help

    Raja Saab

      Hello,

       

      So i am creating a Dashboard type Report that lists the KPIs by Type (Actuals, Budget) horizontally. What i want to do is

      1. Add a row at the bottom that calculates the % to budget for the Actuals

      2. Once i do that i want to color the Actual number cell accordingly - RED is it is below budget, GREEN if above etc.

       

      I am attaching here a screenshot of my worksheet that shows the dimensions and the measures i have

       

      Definition (incase it is not clear from the screenshot)

      KPI - it is the overall KPI (eg in this case Total Members)

      Value Type - is the type of the KPI value (eg. Actual or Budget)

      Week_of_Year - are the weeks of the year

      Value - is the measure (eg.      For Week 1 - 274,808 Actual and 273,724 Budget)

       

      I want to add a row at the bottom that shows the % to budget values(for the example) - for Week One it should show 100.396% ((274808/273724) * 100))

      AND i want to color the Cell of the Actual Value (274808) GREEN.

       

      Any help is much appreaciated!!!

        • 1. Re: Calculation Help
          Mahfooj Khan

          Is that what you wanted?

          If actuals > budget then actuals green else budget red vice verse. I've done some hack to get the percentage because your actual and budget both are in same columns.

          Workbook has been published in my public profile. Have a look.

          | Tableau Public

          Feel free to ask If you've any question.

           

          Mahfooj

          1 of 1 people found this helpful
          • 2. Re: Calculation Help
            Raja Saab

            Mahfooj,

             

            Many Many thanks for the response. I am currently traveling reaching my destination quite late in the night. I saw the workbook and looks like this is exactly what i wanted to achieve. I will download and check it out to my Tableau environment and let you know incase i have questions!

             

            Once again - many thanks

             

            - Vivek (Raja)

            • 3. Re: Calculation Help
              msa s

              Hi Mahfooj,

              Could you explain about this ,

              you are searching up for the max value from first and checking with max value from last    row  ,why you are evaluating to 1

              and also can you share xls sheet ?

               

              IF ATTR([Value Type])='Actuals' AND

              LOOKUP(MAX([Value]),FIRST())>LOOKUP(MAX([Value]),LAST())

              THEN 1

              • 4. Re: Calculation Help
                Mahfooj Khan

                If you see the last line of requester's requirement where requester wants

                to color the actual If its above budget. Integer is much faster then string

                in calculations. So make it more efficient I've used integer. I hope you

                get my point.