5 Replies Latest reply: Jul 14, 2011 4:20 PM by Steve Dark RSS

    Percent of Average

      All I have create the following pivot table.  I Need to add a row which calculates the percentage of the gender average and school average.  The average is based on the average divided by 10.  therefore male percentage = 45% and female percentage= 55% school percentage =55%

       

      Percentage should display below average.   Can someone help me do this?

       

      Scores.JPG

        • Percent of Average
          Steve Dark

          Hi there - I am not sure I follow how you can turn the average into a percentage like that, but you can get a percentage figure by adding a new expression with the calculation for percentage in.  You then need to define on the Number tab that it is a percentage (rather than a decimal) with the Percentage tick box.  This will then add another column for the percentages and show them alongside your averages (rather than underneath), but that is how the Pivot display works.

           

          - Steve

            • Percent of Average

              Can I achieve the same view using something other than a pivot table?

                • Percent of Average
                  Steve Dark

                  You can get more flexibility with a straight table and multiple expressions.  What you will not be able to acheive that way though is the grouped dimensions - like you have CRW in your example.

                   

                  What you may need to look at is the SecondaryDimensionality function to have a different expression based on whether it is in a Pivot total cell or not.  What you will not be able to do with this though is have a different number format in the dimension cells to the total cells.

                   

                  - Steve

                    • Percent of Average

                      Can you walk me through the first scenario I am a newbie.  Thanks

                        • Percent of Average
                          Steve Dark

                          The straight table approach would be fiddly at best to describe.

                           

                          Your best bet is probably to have the Pivot as you presently have with two expressions.  One with the values as you have them, and the other with the percentage.  You can then hide the percentages from the cells that are not totals by setting the text colour of non-total cells to white on the percentages.

                           

                          To do this create both expressions, then click the plus next to the Percentage expression, select Text Colour then copy and paste the following into the Definition box:

                           

                          =if(SecondaryDimensionality() = 1 and Dimensionality() = 1, white(), null())

                           

                          Hope that helps.

                           

                          Steve