Change amount sign based on text in column in another related table

[sylwestercz]

Hello All,

I am new to Power Pivot / PowerBI and despite I consider myself as an Excel Pro still struggle with complex calculations/relationships etc.  I hope you can help me with below matter.

To simplify:

There are 2 tables.

Table 1 = Sales Invoice Line

This table has been appended with Sales Credit Memo Line so there is a list of sales invoices and credit notes however all amounts are positive and there is no other column in that table which could be the key to change sign etc.

Table 2 = Sales Invoice Header

This table has been appended with Sales Credit Memo Header and there is a list of sales invoices and credit notes and here I have a column No_Series with either "Invoice" or "Credit Note" text.

Both tables are not directly related but they both have relationship with Calendar table.

Is it possible to create a calculated column in Table 1 = Sales Invoice Line with an IF statement that when column No_Series in Table 2 is "Credit Note" then multiply amount with -1 otherwise return the original amount?

I tried to explain it as best as possible, please let me know if it was not clear enough.

Thank you in advance,

Sly

[Vvelarde]

hi @sylwestercz

Exist a unique Row for each date in Sales Invoice Line and Sales Invoice Header; No duplicates dates.?

How you do made the relationship in Excel when you search something in both tables?

[sylwestercz]

hi @Vvelarde

Thanks for quick reply.

The thing is that there are none unique rows in Sales Invoice Line and Sales Invoice Header (both are data tables, not lookup tables). Currently the numbering series is different for Invoices and Credit notes but when we implemented system the numbering series was the same, for example:

31000001 - it is an invoice

31000001 - it is a credit note as well

so there are duplicates.

After few months we realized that it is not correct so currently all credit notes have letter "C" in front of the number.

It is so annoying not to know how to fix this 🙂

[Vvelarde · Accepted solution]

Hi @sylwestercz

try with this

Amount-Transform = if(RELATED('Sales Invoice Header'[NO_Series])="Credit note";'Sales Invoice Line'[Amount]*-1;'Sales Invoice Line'[Amount])

[Vvelarde]

If this doesn't work please post a reduced examples of your tables and the relationship between those tables.

[sylwestercz]

Hola @Vvelarde

When I first tried your formula I got an error as proper relationships were not in place. Then I gave a second thought to my tables and decided not to append Sales Invoice Header Invoice & Credit notes as I only need to know which one was a credit note and change the sign. Therefore I created relationship between Sales Invoice Line and Sales Credit Memo Header and then your formula worked well.

Thank you for your help in this matter. I better understand RELATED now.

Greetings from Sweden!

Gracias por tu ayuda en este asunto 🙂