Forum Discussion
Subtract a value from the same column
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?
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.
9 Replies
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 __ResultGreg_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 __Resultchat_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_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] )Hi,
By any chance, do yuo have a Date column in your table? If yes, then please share the table with the date column.
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
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.
