10 Replies Latest reply on Aug 1, 2016 1:51 AM by Ashish Chaudhari

    LOD Calculation error

    Somok Sarkar

      Hello Everyone,

       

      I am trying to achieve a calculation where I am facing some difficulty. PFA the workbook.

       

      The Calculated field is "Calibration %" which needs attention

       

      The requirement here is I need to find the "count of Blue boxes/count of (Blue+Orange) Boxes."

       

      So as per the report for Accessories it should be 4/7 which is 57.14%

      for Appliances it should be 2/5 which is 40%

       

      Regards

        • 1. Re: LOD Calculation error
          Ashish Chaudhari

          Hi Somok,

           

          Please find the calc and confirm the output by looking at the screenshot.

          SUM({ FIXED [Sub-Category] , [Country] : (if SUM([Sales])>1000 then COUNTD([Country]) END) }) /

          SUM({ FIXED [Sub-Category],[Country] : COUNTD([Country]) })

           

          Thanks and Regards,

          Ashish Chaudhari

          2 of 2 people found this helpful
          • 2. Re: LOD Calculation error
            Somok Sarkar

            Hi Ashish,

             

            Many thanks for your response. That's exactly what I was looking for.

             

            Regards,

            Somok

            • 3. Re: LOD Calculation error
              Ashish Chaudhari

              Hi Somak,

               

              You are always welcome. (Thanks for coming up with unique problems all the time. That's what makes community better and better. Keep it up.)

              Happy To Contribute to ppl and this community.

               

              -Ashish

              • 4. Re: LOD Calculation error
                Somok Sarkar

                Hi Ashish,

                 

                Sorry to reply you lately on this. I forgot to mention the main scenario. I checked the calculation result and that was correct.

                 

                Now the calculation which you have given I need to show that beside the Color bars. Something like this below. Currently I am not able to do this on a single worksheet. I have to avoid using it on a dashboard.

                 

                • 5. Re: LOD Calculation error
                  Ashish Chaudhari

                  Hi Somak,

                   

                  I actually tried what you said but I am not able to do so. Please refer to the below screenshot where I am trying to calculated the Sub-Category-wise Country count above Target and trying to divide it by Total countries for that sub category. I am getting wrong count of country which are above target. I am not sure how to do this.

                   

                  For Eg. Consider 1st subcategory i.e Accessories There are actually 4 Countries are above target and 3 are below target. I get the same result when I go to underlying data I get count as below.

                  I get 4 countries which are above target but count I get as 3 (Refer countries above target column).

                  When I tried digging more, by going in to actual raw data. I found below result where i get result as below. (Aus, Austria, AFG are above target)

                   

                  I am still trying.

                   

                  -Ashish

                  1 of 1 people found this helpful
                  • 6. Re: LOD Calculation error
                    Ashish Chaudhari

                    Hi Somok,

                     

                    Please find the updated attached workbook. There was a problem with how you were defining the sales target as Sum(sales) >= 1000 which I have fixed. Please make a note of all the calculations.

                     

                    1. Sales Checker

                    IF { FIXED [Sub-Category],[Country] : SUM([Sales])} >= 1000

                    then "Above Target"

                    else "Below Target"

                    END

                     

                    2. Calc_AT_Di_Total (very Stupid name)

                    { FIXED [Sub-Category] : COUNTD(if [Sales Checker]="Above Target" THEN ([Country]) END) }

                    /

                    { FIXED [Sub-Category] :COUNTD([Country]) }

                    Please find the attached result and verify the output that you require.

                    I hope this one helps.

                     

                    Thanks and Regards,

                    Ashish Chaudhari

                    1 of 1 people found this helpful
                    • 7. Re: LOD Calculation error
                      Ashish Chaudhari

                      Hi Somak,

                       

                      I have posted the answer. I am not sure if you can see that post since, I can see a notification/warning that post is being moderated. So pasting calcs here as well.

                       

                      1. Sales Checker

                      IF { FIXED [Sub-Category],[Country] : SUM([Sales])} >= 1000

                      then "Above Target"

                      else "Below Target"

                      END

                       

                      2. Calc_AT_Di_Total (very Stupid name)

                      { FIXED [Sub-Category] : COUNTD(if [Sales Checker]="Above Target" THEN ([Country]) END) }

                      /

                      { FIXED [Sub-Category] :COUNTD([Country]) }

                       

                      Refer below screenshot.

                       

                      Thanks and Regards,

                      Ashish Chaudhari

                      • 8. Re: LOD Calculation error
                        Somok Sarkar

                        Hi Ashish,

                         

                        That's awesome...

                         

                        Many Thanks for your efforts.

                        • 9. Re: LOD Calculation error
                          Ashish Chaudhari

                          You are welcome.

                           

                          -Ashish