Map a LocalAccount to GlobalAccountPositive or GlobalAccountNegative based on the sum of the Amount

[katemarkoska]

Hi,
I'm trying to do a Balance Sheet report and I need to map Local Accounts to GlobalAccounts.
If the sum(Amount) for the LocalAccount of all data until the selected period is >=0 then map it to GlobalAccountPositive, if the sum(Amount) is less than 0, than map it to GlobalAccountNegative. 
I tried doing this with a calculated column in DAX

VAR Positive = VALUE('GenLedgEntries'[GlobalAccountPositive])

VAR Negative= VALUE('GenLedgEntries'[GlobalAccountNegative])

VAR maxdate=MAX('Date'[datekey])

[Anonymous · Accepted solution]

Hi  @katemarkoska ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _sum=
SUMX(ALLSELECTED('Table'),[Amount])
return
IF(
    _sum>= 0 ,MAX('Table'[GlobalAccountPositive]),MAX('Table'[GlobalAccountNegative]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly