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

You need to first add an index to your table so you can compare rows.

Then you could create a new custom column with a formula something like this:

if #"Added Index"{[Index]+1}[E] = [E] then
  if [D] < [C] then
    (#"Added Index"{[Index]+1}[B] + [B]) / [A]
  else
    [B] / [A]
else 
  ??
  • rmattern's avatar
    rmattern
    Frequent Visitor
    8 years ago

    Thanks but I think you need more information.  The uniqueness per row is based on column E and the column to the left of C.  Also there is not always just 2 rows per column E. The number of rows per column E could be upto 10. 

    • rmattern's avatar
      rmattern
      Frequent Visitor
      8 years ago

      I added an index column named ID and I am trying to write the code for the "Custom Column" but it errors out.

       

      Error: The name 'ID" wasn't recognized.

       

      = if #"ID"{[Index]+1}[Submittal Number] = [Submittal Number] and #"ID"{[Index]+1}[Submittal Pass] = [Submittal Pass] then
      if [Actual Submission Date] <= [Due Date] then
      (#"ID"{[Index]+1}[#"Deliverables ""Accept"] + [#"Deliverables ""Accept"]) / [Deliverables]
      else
      0
      else [#"Deliverables ""Accept"] / [Deliverables]

       

      Data