cancel
Showing results for
Did you mean:  Helper IV

## Transfer an amount from one category to another

Hello,

I would like to transfer the amount of one of the categories to another. For example, I have the following table:

 CATEGORY AMOUNT 100 250 200 180 300 300 400 500 500 260

For example, I would like to transfer from category 400 to category 500 the amount of 500, having in category 400 the amount of 0 and in category 500 the amount of 760, that is to say, the sum of 260 + 500 (amount transferred ).

Is this possible to do? If so, how could it be done?

Thank you very much and best regards.

1 ACCEPTED SOLUTION  Resolver I

Hi @Isidro,

you could achieve your desired outcome with another column specifiyng the correct sign of the account (asset = -1, liability = +1) And the following measures:

```Base Sum:=SUM(FactBalance[Balance])
Correct Sign:=SUMX(DimAccounts,DimAccounts[Sign]*MAX(0,DimAccounts[Sign]*[Base Sum]))
Incorrect Sign:=SUMX(DimAccounts,DimAccounts[Sign]*MAX(0,DimAccounts[Sign]*CALCULATE([Base Sum],FILTER(ALL(DimAccounts),DimAccounts[Account]=EARLIER(DimAccounts[Negative Allocation])))))
Balance Measure:=[Correct Sign]+[Incorrect Sign]```

here is the updated File:
https://www.dropbox.com/s/8xxtehtish67dfi/Balance%20Transfer%20Example%20v2.xlsx?dl=0

12 REPLIES 12  Microsoft

Hi @Isidro,

You could try this formula as a calculated column.

```NewAmount =
VAR PreviousCategory =
CALCULATE (
MAX ( 'Table1'[CATEGORY] ),
FILTER ( 'Table1', 'Table1'[CATEGORY] < EARLIER ( Table1[CATEGORY] ) )
)
VAR PreviousAmount =
IF (
ISBLANK ( PreviousCategory ),
0,
CALCULATE (
VALUES ( Table1[AMOUNT] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[CATEGORY] = PreviousCategory )
)
)
RETURN
IF (
'Table1'[AMOUNT] < 0,
0,
IF ( PreviousAmount < 0, 'Table1'[AMOUNT] + PreviousAmount, Table1[AMOUNT] )
)``` Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Helper IV

Thank you very much for your help. I may not have explained myself very well.

I have two tables:

• A table with the accounting information of the company, ie a newspaper.
• A master table with accounting accounts. This table lists all accounting accounts as unique values ​​and then to the different levels that they belong to. For example, account 572 belongs in level 1 to the Activo, to level 2, to Activo Corriente and to level 3 to Efectivo.

These two tables are related by the accounting accounts.

In Power BI I use a visualization (Matrix preview) for the creation of a table that represents the balance of the company. To do this, in Rows I take the levels of the master table for the creation of the sections that indicate the general Spanish accounting plan (Activo No Corriente, Activo Corriente, ...) and Values ​​the different accounting accounts found in the daily chart.

In the specific case that I tell you, what happens to me is that in this balance there is a negative section because the accumulated amount in the visualization is negative and what I want is that in that case the amount be transferred to another account (in my case the negative balance is from account 572 and I want it to be transferred to account 520.

The solution that I look for is to be done with measurement and not with calculated column since in the movements of account 572 that appear in rows can be given negative balances and what I want is to change the balance of account if the total is negative.

For reasons of confidentiality I can not publish the visualization and what I publish is how the relationship is and an example of the visualization I make. Account 572 is included in the Current Cash Assets section, which in my case, is negative.

Thank you very much and best regards.     Resolver I

@Isidro, okay I think I understand what you are trying to do.

What is missing though is a column that has the account number that the balance should be moved to. I.e. if account 572 is negative, to which account should this negative balance be transferred.

I created a very simple PowerPivot Model to illustrate my idea. (linke here: https://www.dropbox.com/s/8xxtehtish67dfi/Balance%20Transfer%20Example.xlsx?dl=0) The measures are fairly straightforward I hope. The trick is to split the calculation into negative and positive part and then for the positive part use the "Negative Allocation" column to assign the value to a different account.

`Base Sum:=SUM(FactBalance[Balance])Positive Sum:=SUMX(DimAccounts,MAX(0,[Base Sum]))Negative Sum:=SUMX(DimAccounts,MAX(0,-CALCULATE([Base Sum],FILTER(ALL(DimAccounts),DimAccounts[Account]=EARLIER(DimAccounts[Negative Allocation])))))Balance Measure:=[Positive Sum]+[Negative Sum]`  Helper IV

Hi @kschaefers,

First of all, let me tell you that I am very grateful for your great help.

The solution you bring is very close to what I'm looking for. What I did not clarify was that the balances of the asset accounts are positive and the balances of the liability accounts are negative.

In my case, the account that has the negative balance is active and that balance I want to take to a liability account.

Using your example, it is as if the 100, 200 and 300 accounts are assets and the 400 and 500 accounts are liabilities.

What I look for is like the image I attached. Thank you very much and best regards.  Resolver I

Hi @Isidro,

you could achieve your desired outcome with another column specifiyng the correct sign of the account (asset = -1, liability = +1) And the following measures:

```Base Sum:=SUM(FactBalance[Balance])
Correct Sign:=SUMX(DimAccounts,DimAccounts[Sign]*MAX(0,DimAccounts[Sign]*[Base Sum]))
Incorrect Sign:=SUMX(DimAccounts,DimAccounts[Sign]*MAX(0,DimAccounts[Sign]*CALCULATE([Base Sum],FILTER(ALL(DimAccounts),DimAccounts[Account]=EARLIER(DimAccounts[Negative Allocation])))))
Balance Measure:=[Correct Sign]+[Incorrect Sign]```

here is the updated File:
https://www.dropbox.com/s/8xxtehtish67dfi/Balance%20Transfer%20Example%20v2.xlsx?dl=0  Helper IV

What you have proposed is approaching the solution but it has a little left. The transfer of balances makes me very good but for the rest of accounts does not go well.

This is because in each of the accounts, whether asset or reability, there are both positive and negative movements.

These movements can best be seen in the following image: Thank you very much and best regards.  Resolver I

The formulas I provided check the sum at the account level. If for an asset account the sum is negative it is allocated to indicated liability account. If it is positive it is simply shown. The opposite happens for a liability account.

So it doesn't really matter what the individual transactions are like, only the sum of all transactions for one account is checked.  Helper IV

The solution you have given me is very useful. I just have to adapt the DimAccounts table to the peculiarities of Spanish accounting.

Thank you very much and best regards.

Isidro  Resolver I

Hi @Isidro,

this seems like a strange request. is there any rule to move amounts? Or maybe a conversion table, where you specify which source category should move to which target category? Then you can probably do this in PowerQuery.

You could also use a dimension table that assigns a new Category "500" to both "old Categories" 400 & 500.

For adhoc data manipulation (you don't have a rule here), this shouldn't be done in Power BI, but rather in the source data.  Helper IV

Hi @kschaefers,

There really is a rule. The rule is the sign of the amount. The most appropriate would have been a table like the following:

 CATEGORY AMOUNT 100 250 200 180 300 300 400 -500 500 260

In this case, it would be that if the amount of the category of 400 is negative be transferred to category 500.

Thank you very much and best regards.  Resolver I

So if amount for category 100 is negative that amount should be in category 200?

What if Category 200 then becomes negative? Does it then roll up even further to category 300?

This seems like a very odd scenario to be honest . Maybe you can explain the business context for your problem. A different solution might be more appropriate then.  Helper IV

Hi @kschaefers,

You're right. My question is about an accounting table. There are some items that are active and others are passive. In this case, if the balance of an asset account is negative (example 40), I want to transfer it to a liability account (example 500).

I hope I have clarified your doubt.

Thank you very much and best regards.  