Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Transfer an amount from one category to anothe...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Transfer an amount from one category to another

07-04-2017
01:21 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
03:25 AM

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

Hope this helps. Cheers, Konrad

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-04-2017
10:48 PM

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.*

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2017
08:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2017
10:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-14-2017
05:16 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
03:25 AM

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

Hope this helps. Cheers, Konrad

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
09:28 AM

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:

Thank you very much and best regards.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2017
11:44 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2017
02:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-04-2017
01:36 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-04-2017
01:53 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-04-2017
02:09 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-04-2017
02:20 AM

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.