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

Calculating percentage using aggregate measures

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

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

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

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

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

Hi Shah,

Please avoid aggregating an already aggregated value.

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

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