Subtract a value from the same column

[chat_peters]

Hello, 

I am a little stuck here. I have a table below and I want to carry out the following subtract operation

1) Column A minus Column B for Index = 1

2) For every entry after that I want Column C minus Column B (Starting at index 2)

I am trying to reproduce Column C in power bi. Can someone please help?

[Greg_Deckler · Accepted solution]

@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

[Ashish_Mathur · Accepted solution]

Hi,

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.

[Ashish_Mathur]

Hi,

By any chance, do yuo have a Date column in your table?  If yes, then please share the table with the date column.

[chat_peters]

@Ashish_Mathur I don't have a date table. There's really no data model. I just have this one table and I am trying to make this calculation work. I thought the index column should be good enough

[Ashish_Mathur · Accepted solution]

If you want a measure, then refer to the attached file.

[Ashish_Mathur · Accepted solution]

Hi,

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.

[chat_peters]

@Greg_DecklerThank you for getting back.  I tried this one but I get an error for the last part stating that I used a wrong type of parameter into SUMX. I can't pass __Table variable into SUMX also by [__B] do you mean column B? or should I specify variable __B

VAR __Result = __Max - SUMX( __Table, [__B] )

[chat_peters]

@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 🙂

[Greg_Deckler · Accepted solution]

@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

[Greg_Deckler]

@chat_peters My bad!

Column C (Column) =
  VAR __Index = [Index]
  VAR __Table = SUMX( FILTER( 'Table', [Index] <= __Index ), [Column B] )
  VAR __Max = MAX( 'Table'[Column A] )
  VAR __Result = __Max - SUMX( __Table, [__B] )
RETURN
  __Result

[Greg_Deckler]

@chat_peters Try this:

Column C (Column) =
  VAR __Index = [Index]
  VAR __Table = SUMX( FILTER( 'Table', [Index] <= __Index ), "__B", [Column B] )
  VAR __Max = MAX( 'Table'[Column A] )
  VAR __Result = __Max - SUMX( __Table, [__B] )
RETURN
  __Result