7 Replies Latest reply on Dec 2, 2019 11:59 AM by Soumitra Godbole

    3 fields calculated in a KPI

    yunior batista

      I have a view that gives me a list of the states that represent a% of sales according to a parameter.

      I would like to make 3 fields calculated in a KPI to:

      the number of states = 7

      Percentage = 59.36

      sales = $ 1,363,545

        • 1. Re: 3 fields calculated in a KPI
          Tim Cady

          Apologies, Yunior. From your explanation I'm not understanding the expected result.


          Can you provide an example of what you expect this KPI to return?

          • 2. Re: 3 fields calculated in a KPI
            yunior batista

            What I want is to return the view values. as you change the parameter "running total pecentage target"

            in this case it dies in 60%

            therefore KPIs must result in:

            Cant State: 7

            Percen: 59.36%

            Sum: $ 1,363.54

            • 3. Re: 3 fields calculated in a KPI
              Nagarajan  K

              Hi @ batista


              Ya I am understood


              I will try



              Nagarajan K

              • 4. Re: 3 fields calculated in a KPI
                Soumitra Godbole

                Hi Yunior,


                    Hola Amigo, Como estas ? Thanks for such a wonderful problem that has

                brightened my day (which has been great so far). I have given it a 5 star rating

                (* * * * *) just for the fact that it was brilliant and thought provoking.


                   Here is my super cool solution to this wonderful problem. The logic used was

                simply using a Reference line and setting the y axis range from 0 to a trillion.

                The rest was just cosmetic enhancement (NPI ) of the visual by removing

                unnecessary lines and giving it a good look.




                You did have a great start (Bravo ) and your logic was sound.The first sheet

                worked absolutely fine and so I removed your other three sheets and used the

                logic for the first sheet in the remaining 3 and got it to work. Here are 2 other

                cases when we select 60% and 40% for the Parameter.


                For 60% Target (Parameter)


                Pic 2.png



                For 40% Target (Parameter)


                Pic 1.png


                Hope this was helpful and answered your question. Do let me know if you have any questions or need

                further clarity regarding any calculated fields. Best Wishes !




                • 5. Re: 3 fields calculated in a KPI
                  Soumitra Godbole

                  Oops ! The twbx file did not get attached in the earlier post. Anyway, here it is (version

                  2019.2) and do let me know if this works. Also in case it is the right answer to your question,

                  please mark as helpful, correct answer. Muchas Gracias !





                  • 6. Re: 3 fields calculated in a KPI
                    yunior batista


                    1 of 1 people found this helpful
                    • 7. Re: 3 fields calculated in a KPI
                      Soumitra Godbole

                      Sure Yunior ! Here are the steps and hope they make sense:


                      1) I believe you only need one calculated field that I created (Running Total Sales)

                          as you have already created the others. Also your Percentage field works great

                          (LOD Calc) so no need to change that.

                      Formula 1.png

                      2) In the visual shown below (Hoja 2 or Sheet 2), pay attention to the table calculations

                      both in the fields and filters.




                      The Table Calculations have to be calculated across State as shown below


                      Table Calc Details.png


                      Hopefully this was easy. I had to remove the Grand Total as it

                      ave you a fixed value and so I used the Running Total Field.


                      No comes the fun and challenging part i.e. creating the Sum,

                      Count & % value. No worries, I'll show you how easy it is to do for one

                      ie the Sum. You should be able to replicate this logic for the other

                      3 cases.


                      Sum (Total base don Parameter Target Setting):

                      Graph 1.png


                      Simply place the fields in the respective shelf to see the above view. For central vertical alignment use the

                      large axis vales (both + and -). If you wish to align it with the bottom instead, simply use the large value for

                      + axis and 0 for the minimum.


                      Add Reference Line for Sum and do a bit of formatting to get what I displayed earlier. Hope this helps. In case

                      this is the right answer to you question, please mark as helpful, correct answer. Gracias Amigo !