'Total number of times a value is greater than the values in a column
I am trying to determine the total number of times a value within "Current Actual" is greater than the values in column "Original Baseline".
Column "C" within the image below, is the result I am trying to achieve.
The first value in column "Current Actual" is only greater than 1 value in column "Original Baseline". The second value in column "Current Actual" is greater than two values in column "Original Baseline".
The only solution I found is to add an index column and create an if statement to reference the previous row using the column index, if there is an error. Is there a more efficient way to achieve the result?
= Table.AddColumn(#"Changed Type2", "C", each if [Current Actual] > [Original Baseline] then [Index] else if [Current Actual] > #"Changed Type2"{[Index]-2}[Original Baseline] then [Index]-1 else if [Current Actual] > #"Changed Type2"{[Index]-3}[Original Baseline] then [Index]-2 else if [Current Actual] > #"Changed Type2"{[Index]-4}[Original Baseline] then [Index]-3 else if [Current Actual] > #"Changed Type2"{[Index]-5}[Original Baseline] then [Index]-4 else if [Current Actual] > #"Changed Type2"{[Index]-6}[Original Baseline] then [Index]-5 else if [Current Actual] > #"Changed Type2"{[Index]-7}[Original Baseline] then [Index]-6 else 0)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|