2 Replies Latest reply on Jan 22, 2014 3:28 PM by Kevin Denman

    How do you write a custom table calculation that calculates the percent total of a set?

    Kevin Denman

      Hi All,

       

      The blue below represents 'Base Sales' and the other colors on the top represent sales attributed to marketing efforts. Sales attributed to marketing efforts are referred to as 'Lift'.

       

      I need to calculate the % of Total 'Lift' and ignore the base sales in the calculation. I think I need to use a set within a calculated field, but am having trouble.

      PercentOfSet.pngof

        • 1. Re: How do you write a custom table calculation that calculates the percent total of a set?
          Joshua Milligan

          Kevin,

           

          From your screenshot, I think you could create either a set or an ad-hoc group of Tactic. 

           

          The set would have the benefit of being able to be used in a calculated field - but the ability to use the In/Out features required in your calculation are not available with live connections to data sources using the MS JET driver (Excel, Access, text files -- if using those, you'll need to extract).  With a set, create the set where Base is in the set (then everything else is out by default and you won't have to worry about any Tactics that change name or come in as new values later).

           

          Place the set on Columns to divide between Base and everything else.  That way your table calc can work Table Down.  Alternately, you could place the set on the Level of Detail and then edit the table calculation to make sure it is partitioned by the IN/Out Set field.

           

          Hopefully, that helps.  If you are still stuck at some step, please post a packaged workbook, and I be happy to take a look!

           

          Regards,

          Joshua

          • 2. Re: How do you write a custom table calculation that calculates the percent total of a set?
            Kevin Denman

            Thanks, Joshua.

             

            I attached a packaged workbook to my post. I created a group for tactics excluding the base. Using the table I can't seem to get the Table calculation to ignore the base.

             

            When I do Percent of Total, summarizing the values from Tactics (no base) it says they are all 100%.

             

            I appreciate the help!

             

            Kevin