6 Replies Latest reply on Apr 15, 2018 10:55 PM by F Shah

    Calculating percentage using aggregate measures

    F Shah

      Hi,

       

      Very, very new to Tableau.

       

      I have two aggregate measures that I have calculated - Applicants (CountD of id) and Offers (CountD of id for a number of conditions)

       

      I want to calculate the Applicant-to-Offer rate . I cannot figure this out. I keep getting the error "Argument to SUM is already an aggregation, and cannot be further aggregated."

       

      I would really appreciate help. Because of confidentiality, I cannot share the data.

        • 1. Re: Calculating percentage using aggregate measures
          Shinichiro Murakami

          Hi Shah

           

          I understand you cannot share the actual data.

          But it's quite difficult to understand your issue without seeing any data.

          Please add some sample data and your expected result.

           

          Regards,

          Shin

          • 2. Re: Calculating percentage using aggregate measures
            F Shah

            Okay, will try my best. So,

            I have 4  criteria and 3 terms under each criteria. e.g.

                                            P1                                                              P2

                                Term1   Term2  Term3  Term4           Term1  Term2  Term3  Term4

            Appl             1000     1500

            Offers          900        1100

            Accepts        800       900

             

            Here,

            Applicants = countd(id)

            Offers = countd(id) where (field1 = " " and  " "  and " " and " ")

            Accepts = countd(id) where  (field2 = " " and  " "  and " " and " ")

             

            I need to calculate and display the Offers as % of Appl and Accepts as % of Offers.

             

                                                           P1                                                              P2

                                          Term1   Term2  Term3  Term4           Term1  Term2  Term3  Term4

            Appl  (#)                1000     1500

            Offers_to_appl       90%     1100

            Accepts  (#)           800       900

            Accept_to_offer     88%

             

            Hope this will help. Thanks in advance for helping.

            • 3. Re: Calculating percentage using aggregate measures
              Kawaljeet kaur

              Hi F Shah

               

              As to understand your query more properly a sample data set is needed but the error i.e "Argument to SUM is already an aggregation, and cannot be further aggregated."

              is because sum is aggregated function.

               

              for eg: if you do count distinct you will get aggregated value i.e it will display a single number that will show distinct count of that particular calculation. now if u do any sum or average on this . you will get error that value is already aggregated.

              this is because sum will happen if u have more than one value but your count distinct is resulting in single value therefore it can't sum it.

               

               

              Regards,

              Kawal

              • 4. Re: Calculating percentage using aggregate measures
                Paul Field

                When you are creating you applicant to offer rate are you doing something like

                SUM(applicants)/SUM(Offers) ?

                 

                If so - the issue is you are doing a sum of a count. As the message says, you can't aggregate and aggregate. So you should be fine if you just remove the sum from that calculation and have

                [Applicants]/[offers]

                • 5. Re: Calculating percentage using aggregate measures
                  Ritesh Bisht

                  Hi Shah,

                   

                  Please avoid aggregating an already aggregated value.

                   

                  Error "Argument to sum ... is already an aggregation" Creating Calculated Field | Tableau Software

                   

                   

                  1) COUNTD([Customer Name])/ COUNTD([City]) -CORRECT

                   

                  2) COUNTD(COUNTD([Customer Name]))/COUNTD(COUNTD([City]))-INCORRECT

                   

                  Thanks,

                  Ritesh

                  • 6. Re: Calculating percentage using aggregate measures
                    F Shah

                    Thanks Paul. That's exactly what I was doing. Have a 'duh' feeling...