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

# Create New Measure from Existing Measure But Ignoring a Dimension?

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

 Product Trial Payout A 1 0 A 2 4 A 3 8 A 4 10 A 5 4 B 1 6 B 2 4 B 3 3 B 4 1 B 5 2 C 1 7 C 2 6 C 3 4 C 4 7 C 5 6 D 1 3 D 2 0 D 3 8 D 4 9 D 5 8

Intermediate Step (sum values by Trial, ignoring Product)

 Trial Payout 1 16 2 14 3 23 4 27 5 20

Final Output (calculated from intermediate step values)
 Min 14 Q1 16 Median 20 Q3 23 99th Percentile 27 Max 27

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

• ###### 1. Re: Create New Measure from Existing Measure But Ignoring a Dimension?

Hi Jeff,

I have tried to address your requirement by creating a table calculation that runs down the Product and omits the Trial. You will see in the attached workbook that I have created a table calculation that works out a sum for each trial across Products. I have then created another table calculation that references the first one to calculate the median, max etc.

Does this help?

Cheers

Siraj