1 2 Previous Next 24 Replies Latest reply on Jul 19, 2018 9:54 AM by jordi.torrecillascardona

# How to count percentage of total with two dimensions

Hello, I get two dimensions, Customer and Status, for Status I have three options (Malformed, No and Yes) and I have diferent combinations of data depending on every customer,

I have data for all status=no but for others I have customers with or without status data,

What I am trying to do Is create pie charts with percentage of total of status=yes

How can I do that?

• ###### 1. Re: How to count percentage of total with two dimensions

hi Jordi,

Use this calculation

sum(if status ="Yes" then countd(customer) else 0 end) / countd(customer)

if status ="Yes" then countd(customer) else 0 end - this cal will give the 15

Denominator give the  countd(customer) - Count of customer overall it will give the Yes % out of Total

• ###### 2. Re: How to count percentage of total with two dimensions

Thats what Tableau says when I create this calculated fields:

The calculation contains errors:

Cannot mix aggregate and non aggregate comparision or results in ‘IF’ expresions

• ###### 3. Re: How to count percentage of total with two dimensions

My Bad use the below calculation

(if ATTR(status) ="Yes" then countd(customer) else 0 end) / countd(customer)

• ###### 4. Re: How to count percentage of total with two dimensions

Ok, thats what I get with your formula, but I still cant get a pie graph with percentages, what now?

• ###### 5. Re: How to count percentage of total with two dimensions

Hi Jordi ,

Could you please attach small sample excel with data and pie chart output how you want so that it will be more helpful for me to work on requirement

• ###### 6. Re: How to count percentage of total with two dimensions

Like this?

• ###### 7. Re: How to count percentage of total with two dimensions

Hello Joy,

That pie graph is wrong because 27 is the total number of customers,

Any other idea?

• ###### 8. Re: How to count percentage of total with two dimensions

Hello,

Here I attach excel sample to try to find a solution,

What I want to know is total percentage of customers depending on status,

Thanks!

• ###### 9. Re: How to count percentage of total with two dimensions

Hello Joy,

Total customers = 27

What Im trying to create:

1 - total customers (yes) = 15 --- 55% of the total

2 - total customers (without yes) = 12 --- 44% of the total

so, two pie charts with that in tableau

Thanks!

• ###### 10. Re: How to count percentage of total with two dimensions

Hi Jordi ,

Step 1:

Create a calculated field as like

Step 2: Create another calculation to get the %

Step 3: Drag the status to filter and apart from no select another 2 status

Step 4: Drag Status to Rows and Change the marks section to pie and drag customer % value to Text

Hope this helps if you want yes and malformed in the same pie chart then drag the status to Color and % Customer to angle

Check the workbook and Datasource attached for the same

Kindly mark this answer as correct or helpful so that it will help other

BR,

NB

• ###### 11. Re: How to count percentage of total with two dimensions

Hello Naveen,

When I create Step 2 appears that warning:

The calculation contains errors

Argument to sum (an aggregate function) is already an aggregation, and cannnot be further aggregated

• ###### 12. Re: How to count percentage of total with two dimensions

Did you added the step 1 Calculation with "{" "}" Curly Braces

• ###### 13. Re: How to count percentage of total with two dimensions

Hello Naveen, yes my mistake,

Ok, I have followed all your steps but in the last one you can see the percentages does not correspont with the portion of the pie graph,

So I would like to display status=no as the diference between all number of customers minus number of customers with status=yes,

And then display the pie graph with percentages of No and Yes, only that two values,

Do you think thats posible?

• ###### 14. Re: How to count percentage of total with two dimensions

Hey Jordi,

Try LOD:

{ FIXED [Customer] : MAX(IF [Status]='Yes' then 1 else 0 end) }

PFA - Solved workbook.

1 2 Previous Next