4 Replies Latest reply on Mar 10, 2014 7:17 AM by Matt Lutton

    Applying filters at a low-level of aggregation when you want to present summarized data

    Dan Gerena

      I have detail in a table like the sample below, where certain rows do not have a value in the "Plan" column. The "CPI" column simply divides "Plan"/"Actual".


      I want to add a filter such that I exclude any rows with no value in the "Plan" column (i.e. "Plan" is NOT NULL). That's easy enough to do when I am at the most detailed level, like below, but my intent is to summarize this data by "Program Name", without including the "WBS ID" field in the report. Thus my desired end result is to see a computed CPI of 1.18, not 1.68 as I have in the last 2 rows of data below. How do I do this?


      Program NameWBS IDActualPlanCPI (Computed)
      Program AABC0000146,76020,1000.43
      Program AABC000026,2405,9830.96
      Program AABC0000361,31257,7740.94
      Program AABC0000421,373
      Program AABC0000512,976
      Program AABC0000669,312
      Program AABC000073,364
      Program AABC000084,0393,5520.88
      Program AABC00009962
      Program AABC0001024,237
      Program AABC000118,5037,7070.91
      Program AABC000128,2395,6900.69
      Program AABC0001478,92943,7970.55
      Program AABC000153,9771,7600.44
      Program AABC0001614,74625,3321.72
      Program AABC0001715,16016,1071.06
      Program AABC0001851,05447,9890.94
      Program AABC000192,6443,0161.14
      Program AABC0002052,88955,5471.05
      Program AABC0002112,58215,7721.25
      All Rows 499,298310,1261.61
      Exclude Rows with Blanks 367,074310,1261.18