3 Replies Latest reply on Jul 27, 2018 1:47 PM by Gerardo Varela

    LOD Calculation to Sum Certain Values Not Found on Filtered Tab/Column

    Lindon Belshe

      Hi. I am attempting to form an LOD calculation which will yields a percentage of total when some values in that total (denominator) are filtered out and would be from another column. In the attached example, I have seven groups that either send (initiate) an interaction or receive it from the other group...

      For the view (sheet 1) where I have groups A-C listed and the number of records of the receiving column listed, I want to be able to view that groups A is 25.7%, which is 153/(153+166+131+145). Currently, the % of Total has that 153/(153+166+131).

      That means that I need an LOD calculation that includes Group D's sending number of records. While Group D's sending count can be viewed on Sheet 2, I am able to simple indicate that Group D needs to be summed in the LOD calculation as well.

      What I would like is this...

        • 1. Re: LOD Calculation to Sum Certain Values Not Found on Filtered Tab/Column
          Jenell Green

          Hi Lindon,

          To clarify, it looks like the receiving number of records for Group D is 154, not 145?
          You can create a duplicate of the receiving field, I called it "Receiving - denominator" in my example attached. Add this field to your filters, and check 'add to context'. Uncheck any group that you do not want in your numerator nor denominator. Then bring your regular Receiving field also into filters, and uncheck just Group D.

           

          Create another table calc called denominator with the value {SUM([Number of Records])} - This gives you the number of records in the whole window, not just the context. Since the regular receiving field is not set to context, Group D values will still come through.

           

          Create an additional calculated field for the Percent of Total: SUM([Number of Records])/SUM([denominator])

           

          This allows your numerator to only bring in any value in either of the two filters, while the bottom of the calculation is only values in your "receiving" filter, completely irrelevant of your "receiving denominator" filter.

           

          This is a bit hacky! But hopefully it at least steers you in the right direction

          -Jenell

          • 2. Re: LOD Calculation to Sum Certain Values Not Found on Filtered Tab/Column
            Lindon Belshe

            Hi Jenell,

             

            To your clarify question, exactly. The denominator needs to be the sum of the first three groups' receiving and group D's sending. Otherwise, I would use a global filter to filter our groups A-D and then a non-global filter or just hide Group D. Basically, denominator in the % of Total calculation would be the sum of the following...

            • 3. Re: LOD Calculation to Sum Certain Values Not Found on Filtered Tab/Column
              Gerardo Varela

              Hi Lindon,

                See if the attached is what you are after.  If it is and you need me to explain anything just let me know.

               

              Regards,

              Gerardo

              1 of 1 people found this helpful