3 Replies Latest reply on Feb 23, 2018 8:07 AM by swati.jiandani

    Count-Calculated field

    swati.jiandani

      Hello all,

       

      I have a report which shows product name in column. This has 3 products A,B,C.

      I want to create a column calculated field-X- where the condition should be as:

       

      among A, B and C number of products having value >100% should be the count in field X.

      For example if A>100%, B & C <100% Then X=1

      or A <100, B & C>100% then X=2 and if all 3 >100% Then 3 and if all 3 <100% Then 0.

       

      How can i achieve this? What formula can I use to get this calculated field?

       

      Thanks in advance,

      Swati

        • 1. Re: Count-Calculated field
          sunil.sachdeva

          Hi,

           

          Try this in a calculated field:

           

          IF {INCLUDE [Product Name]:(SUM([Value]))} > 100 THEN

          1

          ELSE

          0

          END

          • 2. Re: Count-Calculated field
            swati.jiandani

            Thanks Sunil!

             

            But this formula is not similar to the condition I am looking for.

            I will need to highlight those rows depending on if it's 1 then yellow, 2 green 0 gray and so on

            • 3. Re: Count-Calculated field
              swati.jiandani

              I need something like this:

               

              Region || Territory ||             Product                         Calculated field

                                              Prd-X   ||   Prd-Y || Prd-Z

              East        NY             100.4%     97%     123%                  2

                              NJ              92.6%      84%     45%%                0

                              FL               100%       95.6%    94%                 1

               

              There is a filter on Team. So if I select team1 there are 3 relevant products and if team 2 there can be 4 relevant  products and so on. It doesn't go beyond 4 products for each team. I need a way to get the calculated field column which shows if 2 among 3 products are more than 100% then display 2 and if just one is more then 100% display 1 and none display 0 and if all 3 display 3. Basically its the count of products more than 100% but what would be the calculation to achieve this?