2 Replies Latest reply on Nov 15, 2018 11:01 PM by Swaminathan Jayaraman

    Profit by subcategory

    Raoul Verhaegen

      Hi all,


      I'm currently training for Qualified Associate exam and for some reason I'm not sure how to solve this question :


      Look at the sum of profits for each Product Sub-Category. Which sub-category is $31,069 below the average profit across all categories?


      I understand what this means. So first I've done a calculation that calculates the average profit across categories:

      { FIXED : sum([Profit])/countd([Product Category])}


      And then I've computed profit across all subcategories:

      however, when I calculate the difference, none of them is exactly $31,069 below the average category profit:



      Any help welcome... This is doing my head in

        • 1. Re: Profit by subcategory
          Hideki OGAWA



          Sometimes questions in QA are confusing and this type ones are especially like that, which variables to use to calculate average.

          In this case, this question looks asking the average of the aggregated profit by sub-category.


          There will be two solutions.


          Using table calculation

          Average profit by sub-category (calc) = WINDOW_AVG(SUM(Profit))


          Using LOD

          Average profit by sub-category (LOD) = { EXCLUDE [Product Sub-Category]:SUM([Profit])/COUNTD([Product Sub-Category])}


          The answer should be 'envelope'.



          Best regards,


          3 of 3 people found this helpful
          • 2. Re: Profit by subcategory
            Swaminathan Jayaraman

            Hi Raoul,


            I agree with Hideki. Reading a question slowly a few times will definitely help you understand what it asking you. Another quick way to solve this problem would be to create a bar graph for Profit and Sub-category. Then draw an Average line from the Analytics pane and you should immediately be able to identify the answer as 'Envelopes' as it is $31,069 below the average line.




            Good luck for your preparations.




            1 of 2 people found this helpful