Forum Discussion
Subtract a value from the same column
chat_peters Must not have had enough coffee yesterday. Here is the solution along with a PBIX file (attached below signature) where I include both a column and a measure solution.
Column C (Column) = VAR __Index = [Index] VAR __Minus = SUMX( FILTER( 'Table', [Index] <= __Index ), [Column B] ) VAR __Max = MAX( 'Table'[Column A] ) VAR __Result = __Max - __Minus RETURN __Result Column C (Measure) = VAR __Index = MAX([Index]) VAR __Minus = SUMX( FILTER( ALLSELECTED('Table'), [Index] <= __Index ), [Column B] ) VAR __Max = MAX( 'Table'[Column A] ) VAR __Result = __Max - __Minus RETURN __ResultHi,
If you want a calculated column formula, then this works
Column = Data[Column A]-CALCULATE(SUM(Data[Column B]),FILTER(Data,Data[Index]<=EARLIER(Data[Index])))Hope this helps.
If you want a measure, then refer to the attached file.
Greg_Deckler Thank you for answering. I got an error saying too many arguments were passed into SUMX. I wonder if I should create a virtual table with SUMMARIZE for the first value of column C and then keep subtracting. Any guidance would be greatly appreciated 🙂
chat_peters Must not have had enough coffee yesterday. Here is the solution along with a PBIX file (attached below signature) where I include both a column and a measure solution.
Column C (Column) =
VAR __Index = [Index]
VAR __Minus = SUMX( FILTER( 'Table', [Index] <= __Index ), [Column B] )
VAR __Max = MAX( 'Table'[Column A] )
VAR __Result = __Max - __Minus
RETURN
__Result
Column C (Measure) =
VAR __Index = MAX([Index])
VAR __Minus = SUMX( FILTER( ALLSELECTED('Table'), [Index] <= __Index ), [Column B] )
VAR __Max = MAX( 'Table'[Column A] )
VAR __Result = __Max - __Minus
RETURN
__Result