3 Replies Latest reply on Feb 26, 2013 9:02 AM by Harley Ellenberger

    percent of total within a dimension

    Harley Ellenberger

      In the attached workbook, how would I create a calculated field to calculate the percentage total of each category to the "total" row

       

      For example, Chicago Auto No would be 8.4% [(345526/4091286)*100].  Chicago Auto Yes would be 11.3% [(73790/651247)*100]

       

      I'd like to repeat that calculation for each member of the CATEGORY2 dimension

        • 1. Re: percent of total within a dimension
          Joshua Milligan

          Harley,

           

          I was a little confused with the Total records.  Typically, I wouldn't want the total in the data, I would simply turn on subtotals or grand totals in a text table to see them.  However, when I did that, the numbers didn't match.  For example, the total for Chicago/Yes was approx 317,000 not 651,000.

           

          When using sub-totals, what you are trying to do is fairly easy -- you can use a quick table calculation.

           

          In this case, to preserve your total lines and actually use them, I created a calculated field to compare the value for a given Category2 against the total record.  The code is:

           

          LOOKUP(SUM([UNITS]), 0) / LOOKUP(SUM([UNITS]), Last())

           

          It is a table calculation, so I had to setup the addressing and partitioning correctly (right click the Percent field and "Edit Table Calculation" to see how it is setup.

           

          I've attached the workbook so you can see if it is what you are looking for.

           

          Regards,

          Joshua

          • 2. Re: percent of total within a dimension
            Joshua Milligan

            Harley,

             

            Here it is again using the sub-totals.  If you could use this approach (versus having Total records embedded in the data), it would give you a lot more flexibility and be cleaner (for example a quick filter that filtered out TELECOM would be reflected in the sub-total and the percents would change).  At any rate, the attached workbook has both possibilities.

            • 3. Re: percent of total within a dimension
              Harley Ellenberger

              Thanks Josh.

               

              Yes, the total row is not a "true total" based on my data.  That's why I couldn't get what I needed using a simple table calculation.

               

              Your lookup function will produce the result I need in this case.