1 Reply Latest reply on Jun 27, 2013 1:47 PM by sirajmandayippurath

    Create New Measure from Existing Measure But Ignoring a Dimension?

    Jeffrey Gho

      Hi,

       

      I have 2 dimensions (e.g., product and trial) and 1 measure (e.g., payout). I would like to sum Payout by Trial (so ignoring Product), then perform a calculation (e.g., min, average, median, max, and percentiles) along Trial.

       

      Raw Data

      ProductTrialPayout
      A10
      A24
      A38
      A410
      A54
      B16
      B24
      B33
      B41
      B52
      C17
      C26
      C34
      C47
      C56
      D13
      D20
      D38
      D49
      D58


       

      Intermediate Step (sum values by Trial, ignoring Product)

      TrialPayout
      116
      214
      323
      427
      520


       

      Final Output (calculated from intermediate step values)
      Min14
      Q116
      Median20
      Q323
      99th Percentile27
      Max27

       

      I have tried table calculations without success. I am also interested in creating a new measure/calculated field that is the intermediate step, so that I can create a CDF chart (with "% of Total Running Sum of Count of Payouts" along the y-axis and Payout along the x-axis; however, I cannot seem to "strip" the Product dimension data. With the Product dimension data, the chart uses the Payout number from each Product and Trial combination rather than the Payout aggregated at the Trial level.)

       

      I appreciate any help!

       

      Thanks,

      Jeff