Change value in column based on sum in selected period of time

[Anonymous]

Hello,

I have a problem where I could need your help.

My data consists of financial transactions, where one row is one transaction, and consists of only one account number in the column AccountNo.

My challenge is that for some accounts, the account number should change if the sum of transactions on this account is less than or equal to 0.

A simplified example of what the data looks like:

What I need help to is to create a measure that changes the account number to the one in the AlternativeAccountNo column, if the sum in the column transactionAmount is less than or equal to 0.

The measure needs to depend on the slicer filters that are chosen (in one selected period the sum could be negative, then I need the alternative account, but in another period it could be positive and therefore should not change).

Example on how I want it to be:

Any suggestions on how I could achieve this?



(Edit: changed image on desired outcome, as I discovered an error)

[amitchandak]

@Anonymous , if need a measure account no has to be there in visual

If(Isblank(sum(Table[transactionAmount])) && not(Isblank(max(Table[AlternativeAccountNo]))) , max(Table[AlternativeAccountNo]) , max(Table[AccountNo] ))

[Anonymous]

Thank you for your help, @amitchandak 

I did not get that solution to work, probably I did not explain good enough. I need only account 10001 to change if total sum is less than 0, the other accounts should be as before.

With your suggestion I ended up with something looking like this, where the new column changed for all accounts.

My goal is to end up with something like this, where only the first account changes.

[ryan_mayu]

@Anonymous 

pls try this

alernative = if(max('Table'[AccountNo])=10001,max('Table'[AlernativeAccountNo]),max('Table'[AccountNo]))

[Anonymous]

Thank you, @ryan_mayu 

I need to do this transformation only if the sum of transactions on account 10001 is less than or equal to 0. How can I incorporate that specification into the measure (as I understand the measure it will change the accountnumber for account 10001 no matter what the sum is, but I may be wrong).

And to add a bit more complexity: If I want to change the accountnumber for BOTH accountNo 10001 and 10002 if the sum on both accounts combined is less than or equal to 0 - can I achieve this by using the basics of your measure? Assume the data looks like this:

[ryan_mayu]

@Anonymous 

yes, you are right. the DAX i provided only change the specific account and didnot consider the sum value. 

i still have a question, what you mean is (sum of 10001 + sum of 10002) <=0, then change both account to specific account no. If the result is bigger than 0, then keep the original account no?

[Anonymous]

Yes, thats correct - 

If (sum of 10001 + sum of 10002) <=0, then the new account number for both accounts should be 55555. If >0, then I will keep the original account numbers (10001 and 10002)

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Measure = 
VAR _SUM= CALCULATE(sum('Table'[TransactionAmount]),FILTER(ALL('Table'),'Table'[AccountNo] in {10001,10002}))
RETURN IF(MAX('Table'[AccountNo]) in {10001,10002}&&_SUM<=0,55555,max('Table'[AccountNo]))

[Anonymous]

Thank you so much, @ryan_mayu  -  It seems to work! 

[ryan_mayu]

you are welcome