Forum Discussion
How do I pull out amt changed from cumulating sum while ignoring initial value?
Hi sensemaker14 ,
I adjust something, please try below steps:
1. add a new column with below dax formula
RK = VAR cur_year = [Year] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Year] = cur_year ) RETURN RANKX ( tmp, [Index],, ASC, DENSE )2. use this dax formula to replace above same name measure
Adjust Contribute = VAR cur_rk = SELECTEDVALUE ( 'Table'[RK] ) VAR cur_year = SELECTEDVALUE ( 'Table'[Year] ) VAR cur__ct = CALCULATE ( MAX ( 'Table'[Contributed Capital] ), 'Table'[RK] = cur_rk && 'Table'[Year] = cur_year, ALL ( 'Table' ) ) VAR pre_ct = CALCULATE ( MAX ( 'Table'[Contributed Capital] ), 'Table'[RK] = cur_rk - 1 && 'Table'[Year] = cur_year, ALL ( 'Table' ) ) RETURN IF ( cur_rk = 1, 0, cur__ct - pre_ct )3. then you could get the output
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi sensemaker14,
Please try below steps:
1. add a new index column to the table
2. create two measure with below dax formula
Adjust Contribute =
VAR cur_index =
SELECTEDVALUE ( 'Table'[Index] )
VAR cur__ct =
CALCULATE (
MAX ( 'Table'[Contributed Capital] ),
'Table'[Index] = cur_index,
ALL ( 'Table' )
)
VAR pre_ct =
CALCULATE (
MAX ( 'Table'[Contributed Capital] ),
'Table'[Index] = cur_index - 1,
ALL ( 'Table' )
)
RETURN
SWITCH ( TRUE (), cur_index = 1, 0, cur_index = 2, cur__ct, cur__ct - pre_ct )
Income add Contribute =
VAR cur_income =
SELECTEDVALUE ( 'Table'[Income] )
VAR cur_ac = [Adjust Contribute]
VAR _val = cur_income + cur_ac
RETURN
IF ( _val > 0, "+" & _val, "-" & _val )
3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your response.
I am trying to achieve the values listed in the "Combined" column, while ignoring the inital contributed capital value.
Hi sensemaker14 ,
As my above screenshot show, please refer "Income add Contribute" measure, the logic of his calculation is consistent with what you said.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous
I don't know how to attach the .pbix like you did, it errors and says the file type isn't allowed.But the only thing I'm missing is the initial ignore.
Adjusted Contribute =var current_index=SELECTEDVALUE('Table'[Index])var current__capital=CALCULATE(MAX('Table'[Contributed Capital]),'Table'[Index]=current_index,ALL('Table'))var previous_capital=CALCULATE(MAX('Table'[Contributed Capital]),'Table'[Index]=current_index-1,ALL('Table'))returncurrent__capital-previous_capitalHi sensemaker14 ,
I adjust something, please try below steps:
1. add a new column with below dax formula
RK = VAR cur_year = [Year] VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Year] = cur_year ) RETURN RANKX ( tmp, [Index],, ASC, DENSE )2. use this dax formula to replace above same name measure
Adjust Contribute = VAR cur_rk = SELECTEDVALUE ( 'Table'[RK] ) VAR cur_year = SELECTEDVALUE ( 'Table'[Year] ) VAR cur__ct = CALCULATE ( MAX ( 'Table'[Contributed Capital] ), 'Table'[RK] = cur_rk && 'Table'[Year] = cur_year, ALL ( 'Table' ) ) VAR pre_ct = CALCULATE ( MAX ( 'Table'[Contributed Capital] ), 'Table'[RK] = cur_rk - 1 && 'Table'[Year] = cur_year, ALL ( 'Table' ) ) RETURN IF ( cur_rk = 1, 0, cur__ct - pre_ct )3. then you could get the output
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.