4 Replies Latest reply on Aug 25, 2018 5:15 AM by Okechukwu Ossai

    How to provide sums per line

    Jomi Lemmermann

      Hi community,

       

      I am facing a problem when trying to calculate the total sum per line.

      I got several Units displayed (left rows), which are assigned to my (in this case) funds in the top row.

       

      The calculation "SUM per Unit" only shows the first line again. I am trying to receive the total sum per unit over all funds (preferably on the right). This needs to be a calculated field, not the Analysis button.

      Furthermore, in this type of view it is not possible to display a sum per column (from the "Analysis button") and I have no idea why.

       

       

      Workbook is attached below, any help is appreciated.

       

      Best regards,

      J.

        • 1. Re: How to provide sums per line
          Naveen B

          Hi Jomi,

           

          change your calculation like below

           

          ZN(SUM([Ame-Am1]))+ZN(SUM([Ame-Urf]))+ZN(SUM([Ami-Aap]))+ZN(SUM([Ami-Ama]))+

          ZN(SUM([Amn/Amh]))+ZN(SUM([Asd-Adm]))+ZN(SUM([Asd-Adm]))

          +ZN(SUM([Asd-Ams]))+ZN(SUM([Ash-Amh]))+ZN(SUM([Ash-Ashd]))

          +ZN(SUM([Ash-Ashi]))

           

           

          Including all the measured values and padded with ZN

           

          Hope this helps kindly mark this answer as correct or helpful so that it will help others

           

          BR,

          NB

          • 2. Re: How to provide sums per line
            Jomi Lemmermann

            Hello Naveen,

             

            Thank you for your answer. This calculation now gives me the sum per column. So I get the sums for every funds.

             

            I was actually looking for the sum per line, so the sum for every unit over all funds.

             

            Despite this, your answer was helpful as well, as it solves my second Problem.

             

            Thanks a lot so far,

             

            Kind Regards,

            J.

            • 3. Re: How to provide sums per line
              Naveen B

              Hi Jomi,

               

              Are you expecting like this

               

               

               

              If this is the case --> Analysis --> Totals --> Show row grand totals

               

              Hope this helps

               

              BR,

              NB

              • 4. Re: How to provide sums per line
                Okechukwu Ossai

                Hi Jomi,

                 

                What you are looking for may require a separate calculation for each measure. This may become a bit clumsy. An alternative approach will be to pivot the data. This will depend on your data source. See attached workbook.

                 

                Step 1: Pivot your data

                Step 2: Delete the old individual measures. Rename Pivot Field Names and Pivot Field Values to something more meaningful.

                 

                Step 3: Create calculated fields to sum the values per pivot field name and fondsname.

                 

                [Sum per Pivot Field Name]

                {FIXED [Pivot Field Names]: SUM([Pivot Field Values])}

                 

                [Sum per Fondsname]

                {FIXED [Fondsname]: SUM([Pivot Field Values])}

                 

                Notice that the Pivot Field Values and the calculations give you same result in the screenshots below. So, you won't need the calculations except you want to use the calculated fields for other purposes.

                 

                 

                 

                Hope this helps.

                Ossai