1 Reply Latest reply on Mar 22, 2016 5:48 PM by Esther Aller

    Display Data Based on Volume of Records

    Nick Corbin

      Hi, I have a table where I only want to show data if the total amount of records in any cross tab is greater than 1% of all observations.

       

      Currently I have the below - preferably I'd want to not have to show the percentages at all.

       

       

      Any thoughts would be much appreciated.

       

      Thanks,

       

      Nick

        • 1. Re: Display Data Based on Volume of Records
          Esther Aller

          Hi Nick,

           

          You can make a filter using level of detail (LOD) expressions that will filter out values of [Order Channel] where the percent of total of count of [Age Now] is less than 1%.

           

          1. Create a calculated field with a name like "Percent of Age Observations" and drag the green CNT(Age Now) Δ from the marks card into the calculation editor. Tableau Desktop will automatically write out the calculation for the quick table calculation. The calculation should look similar to:

            COUNT( [Age Now] ) / TOTAL( COUNT( [Age Now] ))

          2. Modify [Percent of Age Observations] to replace TOTAL() with a FIXED expression. This step is necessary so we can nest this calculation in a LOD expression in step 3. The final calculation may look like:

            COUNT( [Age Now] ) / SUM( { FIXED :  COUNT( [Age Now] )} )

            Note: we must wrap the FIXED expression in SUM() so that all arguments are aggregated, but SUM() does not change the value of the FIXED expresssion.
          3. Create a calculated field with a name like "Low Percentage Filter" with a calculation similar to the following:
            { FIXED [Order Channel] : [Percent of Age Observations] } >= .01
          4. Drag [Low Percentage Filter] to the Filters shelf
          5. In the Filter dialog, check True and click OK

           

          Basically these calculations are fixing the percentage of observations to the [Order Channel] level, which means every row within an order channel will have the same (grand total) value. Then we can evaluate if this grand total value is greater than the desired percentage. This method does not rely on how the view is built, therefore it is not necessary to show the percentage in the crosstab.

           

          I hope this answers your question!