Search column1, If column2 is larger than 0 then return value from column 3 if match in column 4

[twanahc]

Hi,

I know the title is a bit long and strange, i hope i can provide with a good explanation here.

I always find it easier to put things into context right at the start and then proceed with what i need your help with so i will do so.

Context

• ColumnX is a column with what product item number that has been sold.
• ColumnY is the quantity of that item sold/returned
• ColumnZ is a invoice number and has a unique value for each specific invoice.
• ColumnA links different types of invoice types together.
• Column B is a column that states what type of invoice it is (Sales/Credit)

A table of this sort would look something like:

I would need your help to create a new calculated column that:

1. Searches through every row.
   
   
2. Checks if ColumnA on that searched row has a number greater than 0.
   Sales that have no credit note/return attached to it has 0 in ColumnA
   
   
3. Returns the value of ColumnY from the corresponding matched invoice number of ColumnZ that is located in ColumnZ if columnX's number matches between the rows.
   
   I am a newbie at this but my attempt to grasp its logic as a concept is:
   If columnA is greater than 0 then search for ColumnA's number in ColumnZ to return the value of ColumnY to the row where columnA was greater than 0 if ColumnX's of both the rows match.

I greatly appreciate any help.

Thank you.

[Anonymous]

Hi

Good that you explained the requirement clearly. Could you please attach the expected result for the given sample data?

Thanks
Raj

[twanahc]

Hey @Anonymous,

I hope i make sense on the attached picture below

This is how i imagine it would look like.

The move thereafter is to create a measured column in the report to generate the net quantity delivered for each invoice.

Thanks,

Twana.

[v-juanli-msft · Accepted solution]

Hi @twanahc

In original table sheet1, create a calculated column

flag = CALCULATE(COUNT(Sheet1[A]),ALLEXCEPT(Sheet1,Sheet1[X]))

Then create a new table

Table1 = SUMMARIZE(FILTER(Sheet1,[flag]>1),[X],[Y],[Z],[B])

Create relationship with them

In original table sheet1, create a column

Column = IF([flag]>1,RELATED(Table1[Y]),0)

Best Regards

Maggie