Forum Discussion
Referencing rows within the same table to calculate a sum
I'm trying to get the sum of a the SO_Transactions[Transaction ARR] column using another column to filter the table based on Transaction Number. This is all within the same table. I'm using the below formula to do this. It is attempting to use the data in the SO_Transactions[Formatted Renews] column to get the rows for the summation. It's coming up blank and I'm not sure why.
5 Replies
- Greg_Deckler
Community Champion
First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Second, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
- amitchandak
Super User
Try
Previous Amt = SUMX(FILTER(SO_Transactions,SO_Transactions[Number] = earlier(SO_Transactions[Formatted Renews])),SO_Transactions[Transaction ARR]) OR Previous Amt = SO_Transactions[Transaction ARR]- SUMX(FILTER(SO_Transactions,SO_Transactions[Number] = earlier(SO_Transactions[Formatted Renews])),SO_Transactions[Transaction ARR]) HI kennemca,
Your 'Formatted Renews' fields are stored the string value instead of the list of text values. For this scenario, 'IN' operator does not work as you expected.
In my opinion, I'd like to suggest you use the search function to compare with two string fields.Previous Amt = SUMX ( FILTER ( SO_Transactions, SEARCH ( SO_Transactions[Number], SO_Transactions[Formatted Renews], 1, -1 ) > 0 ), SO_Transactions[Transaction ARR] )Regards,
Xiaoxin Sheng
