3 Replies Latest reply on Oct 26, 2015 5:44 AM by Thomas McCullough

    Contribution to Growth, Contribution to Decline

    Greg Mooney

      Hello,

       

      I have the following worksheet that I need to create a calculation. This is sales data laid out by segment. I want to know "If" a segment had positive sales growth what percentage of the sales growth segments (remember some segments could have a sales decline so I do not want those numbers in my total).

       

      I have created 2 calculated fields, one showing positive growth and the other negative sales growth.

       

      My issue is that I cannot figure out how to take the segment number and divided it by the grand total.

       

      So I would be looking for this as an answer for Kits of 52.9% The $6,968,744 divided by the $13,164,663

       

      Any suggestions?

       

      I would also like to figure out why the Grand total number of $1,258,142 is not in red?

       

      Capture.PNG

        • 1. Re: Contribution to Growth, Contribution to Decline
          Thomas McCullough

          Hello Greg,

           

          You are definitely headed in the right direction.

           

          Next step, take your logic for the positive growth segments and add them up:

          WINDOW_SUM([Account Growing Sa...]) running along Segment.

          This will return the value 13,164,663.

           

          You can then create another calculation that gives you the percentage share.  The inside would work similar to:

          [Account Growing Sa...] / WINDOW_SUM([Account Growing Sa...]). This will return each segments share of all positive growth sales.

           

          Hope this gets you on the right track!

           

          Tom

           

          P.S. The color for Grand Totals is formatted separately than the rows and columns, so you won't be able to change the color based on any logic.

          • 2. Re: Contribution to Growth, Contribution to Decline
            Greg Mooney

            Thank you,

             

            It does but I think I failed to mention that I just wanted one combined column for my growth and decline contribution. Your suggestion worked perfectly but I have blanks for the decliners. So I went back and adjusted the calc to an if then statement and now all is good with the exception of 2 additional things.

             

            How can I make the negatives go to the left versus both positive and negatives going to the right?

             

            Also, how can I remove the color coding for the totals? They are interfering with my segment color coding.

             

            Thanks,

             

            G

             

            Greg Mooney

            Vice President, Analytics & Insights

            Adesso Solutions

            3701 Algonquin Road

            Rolling Meadows, IL 60008 USA

             

            O:  847.342.1095 x 4024

            C:  831.293.4048

            gmooney@adesso-solutions.com<mailto:gmooney@adesso-solutions.com>

             

            <http://adessosolutions.com/>[cid:image004.png@01D10F34.CA8C1870]<http://www.linkedin.com/company/1344978?trk=tyah&trkInfo=tas:adesso%20solutions>

            Click our logo to go to our website and join the TPE community!

             

            This message (and attachments) is for intended recipient’s use only and contains confidential information. If received in error, please advise sender ASAP then destroy email so it cannot be shared.

            • 3. Re: Contribution to Growth, Contribution to Decline
              Thomas McCullough

              Hi Greg,

               

              I'm not sure I follow you on the question "How can I make the negatives go to the left versus both positive and negatives going to the right?"

               

              To change the color of the grand total, right click in the grand total area and select FORMAT.  You can adjust the appearance in the left-hand pane that just popped up.

               

              Cheers,

               

              Tom