5 Replies Latest reply on Jun 8, 2017 6:56 AM by Zhouyi Zhang

# Conditional SUM of CountD() in Tableau

Hello all,

I'm having trouble summing a distinct count when I drill up in a hierarchy within a table.  Unfortunately I could not attached the workbook as it is confidential but I tried to illustrate that by an example.:

What I want to get is the number of "centers" in a every country by condition on a percentage (sum(profit)/sum(sales)). I have 3 categories in the test: >50%, <30% and Between them (like that:

Under Tableau, At the lowest level of the hierarchy I get the countD of the CenterCode.  However, when I start drilling up in the table I just want a simple sum of the distinct counts on the next level= Country level  but what I get is a wrong sum of values even if I drag CenterCode to detail it gives me the right distribution by category!

(wrong sum!!)

The test has been done is :

IF sum(profit)/sum(sales)> 0.5 then ">50%" ELSEIF sum(profit)/sum(sales)>0.3 then "between 30% and 50%" else "<30%"

END

Any suggestions would be much appreciated.

Hajar

• ###### 1. Re: Conditional SUM of CountD() in Tableau

Hi, Hajar

Not very sure how your countd(Centrecode) manipulate with your sum, can you show the calculation code of them?

ZZ

• ###### 2. Re: Conditional SUM of CountD() in Tableau

Hello,

The calculation for the percentage on wich the test is made is : SUM(profit)/sum(sales) {because in every country, there are many centers, and in every centers, there are multiple products with different profit and sales values).

For the CountD(centerCode) I just drag the CenterCode to Marks and make it CountD and dispaly it in the table.

• ###### 3. Re: Conditional SUM of CountD() in Tableau

so what do you mean by saying sum of countd()? can you give a sample code or make up some data and create a sample workbook so that I can have a look?

ZZ

• ###### 4. Re: Conditional SUM of CountD() in Tableau

Hello,

please find attached a workbook I have created that illustrates the problem I am facing when I try to drill up at country level for total count of stores with condition on a calculated field.

Thanks!

• ###### 5. Re: Conditional SUM of CountD() in Tableau

Hi, Hajar

can you save your workbook as .twbx because I can't see your data with twb

ZZ