5 Replies Latest reply on May 15, 2018 8:16 AM by lenny.bloom

    % of Total calculation by Filter

    lenny.bloom

      Hello,

       

      I am running into a problem where I am being asked to have a % of total calculation related to a filter within my report.  I would like the % of Total sales to be calculated by which employee is selected in the filter.  Using this method, if all employees are selected the % of Total sales should be equal to 100%.  Does anyone have any ideas of how I could create a calculated field or add a specific dimension to the table calculation panel?  Thanks in advance!

       

      Best,

      Lenny

        • 1. Re: % of Total calculation by Filter
          Andrew Watson

          If all employees are selected the total = 100%. Does this mean you would expect the total to be <100% if a selection of employees are selected? Apologies but it's not clear in the question.

           

          If that is the case you'll need to use a late filter. Something like LOOOKUP([EmployeeName],0) as your filter. It'll only work for the sheet it's created, this type of filter can't be applied to multiple worksheets sadly.

          • 2. Re: % of Total calculation by Filter
            Rahul Singh

            Hi Lenny,

             

            Instead of using table calculation, i would suggest first create a field (using lod) which calculates the total sales irrespective of any filters applied.

             

            Total Sales > { FIXED :SUM([Sales])}

             

            Then taking percent of total calculated field which remains same irrespective of filter applied and when all the fields are selected, the total is 100%. Attached example file using superstore data.

             

            percent of Total > SUM([Sales])/SUM([Total Sales])

             

            Regards,

            Rahul

            • 3. Re: % of Total calculation by Filter
              Simon Runc

              hi Lenny,

               

              So if I understand this correctly, the problem is that when you filter to an Employee the %age shows 100%?

               

              In order to get this to calculate as you want the easiest way is to use an LoD. FIXED LoDs are calculated before any regular filter is applied, so can hold the Total number regardless of the filtering. So something like this

               

              COUNTD([Employee]

              /

              SUM({FIXED: COUNTD(Employee)})

               

              Should do the trick. Let me know if that doesn't make sense, or doesn't do what you need.

              • 4. Re: % of Total calculation by Filter
                lenny.bloom

                That is a correct assumption, still working to figure it out.

                • 5. Re: % of Total calculation by Filter
                  lenny.bloom

                  So if only 1/10 employees is selected, it should show a percentage of the total amount that one employee based on their sales.  That percentage in most if not all cases would be less than 100%.