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

    Conditional SUM of CountD() in Tableau

    Hajar kaya

      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!

      Capture.PNG

      (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.

      Thank you in advance.

       

      Hajar