11 Replies Latest reply on Jun 14, 2017 4:25 PM by Shinichiro Murakami

    filter a dimension without affecting table grand total

    Ji Zhou



      I have faculty performance evaluation data at my school, organized by department. Some faculty are not affiliated with any dept, i.e., their dept field is missing (null value). I created a dept filter and wanted to achieve two similar goals:


      1 filter out faculty who are null dept in view but include them in calculating school average,

      2 select any dept from the dept filter without changing the school average.


      In the end, I want to show all depts in view, with dept filter on the side, and readers decide which depts to include in the view, but regardless of how many depts are chosen the grand total/school average stays the same.Please help me out.


      For the null, I could click->hide null. But I was wondering if 1 and 2 can be solved by the same set of calculations, i.e., one solution solves both 1 and 2.


      This is a common problem for a majority of my workbooks. I have to figure this out. Thanks a lot for your help!