6 Replies Latest reply: Apr 6, 2017 7:24 AM by Ryan Woodburn RSS

    Pivot Table Formatting

    Ryan Woodburn

      Hi

       

      I have been having issues when applying RAG formatting to a pivot table.

       

      My table shows staff utilisation per month, and their respective teams.

       

      The expression for the utilisation is as follows:

      SUM([Billable AMS Time])/ SUM([Available AMS Time])*100


      My Dimensions are Team, Person and Month(set horizontally)


      When I have a filter on Month, and Department not expanded, the rule works as expected.


      If I unfilter month, or expand Department, the rule appears for Department appears to use whatever value is in the top left cell for that Department.


      See below:

      Correct

      correct.JPG



      When I expand the department, however, it appears to take the top value for that department, as below:

      incorrect1.JPG


      If I remove the Month filter, I get a similar issue:

      incorrect2.JPG



      I would like the Department column's RAG to reflect the average of each person within that Department, and each Person's RAG to reflect their average across the three months.


      Thanks in advance