1 Reply Latest reply on Sep 18, 2018 11:17 PM by Paul Vincent

    Percentage of total not totaling 100

    Tania Jordan

      Hello all, I have a question on a calculation on a bar chart. I am running a Percentage of Total calculation, but it is not totaling 100%.


      COUNTD([Id Oics]) / TOTAL(COUNTD([Id Oics]))


      This is probably because I am hiding the null data (not excluding it)  as I don’t want it to appear on the chart - so the formula seems to be considering the nulls. I cannot exclude the data as it's needed for another bar chart on the page.


      How can I change the formula so ‘TOTAL’ does not count the ‘Nulls’ when calculating the % for each category?



      It can be found here: https://public.tableau.com/profile/cgiar.system.organization#!/vizhome/CGIARResultsDashboard2018-Aug/1_1DBOICS-CCI?publish=yes


      Thank you,

        • 1. Re: Percentage of total not totaling 100
          Paul Vincent

          Hi Tania,


          This happens because of the order that data is processed. In this case the data is coming back from the database, a table calculation is being carried out and at the last step the null values are being hidden. So the null values are actually being taken into account in the calculations even though you don't see the resulting bar. To solve this simply hide the nulls (right click on the bar and exclude). It will fix the calculation and those values will still be available on other sheets unless you specifically filter them in those other visualisations.


          Hope this helps