Forum Discussion

rmattern's avatar
rmattern
Frequent Visitor
8 years ago

Sum column values based on another column then divide that sum by another column single value.

I need to sum a column conditionally based on another column then divide that sum by another single value from another column.  Looking at the data in the screen clip, I need to sum the numbers in co...
Songbird's avatar
Songbird
Regular Visitor
8 years ago

First you need to create an index column in order to compare rows.  Then you can create a custom column (in Edit Query) with something like the following formula:

if #"Added Index"{[Index]+1}[E] = [E] then
  if [D] < [C] then
    (#"Added Index"{[Index]+1}[B] + [B]) / [A]
  else
    [B] / [A]
else 
  ??