cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Isidro
Helper IV
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:

 

CATEGORYAMOUNT
100250
200180
300300
400500
500260

 

 

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

Hi @Isidro,

 

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

image.png

 

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

 

Hope this helps. Cheers, Konrad

View solution in original post

12 REPLIES 12
v-jiascu-msft
Microsoft
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] )
    )

Transfer an amount from one category to another.JPG

 

 

 

 

 

 

 

 

 

 

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.

Hi @v-jiascu-msft and @kschaefers,

 

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.

 

Relaciones contables.jpgCuadro cuentas contables.jpgBalance Power BI.jpg

@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)

 

image.png

 

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]

 

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.

Traspaso Saldos.jpgThank you very much and best regards.

 

 

 

 

Hi @Isidro,

 

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

image.png

 

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

 

Hope this helps. Cheers, Konrad

View solution in original post

Hi Conrad @kschaefers ,

 

I am very grateful for the help you are giving me.
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:

 

Asset Accounts.jpg

Thank you very much and best regards.

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.

Hi Conrad @kschaefers,

 

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

kschaefers
Resolver I
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. 

 

 

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:

CATEGORYAMOUNT
100250
200180
300300
400-500
500260



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.

 

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 Smiley Happy. Maybe you can explain the business context for your problem. A different solution might be more appropriate then.

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!