New column to get complementary row data if multiple criteria is met

[Anonymous]

Hi,

I want to create a new column within the same table based in 3 criterias&columns that must be met:
-Same Code
-Same Month
-Same Prov.
If they are equal, then the new column will contain the missing data between another 2 columns: Amount and Fact.

Example Table:

Code	Month	Prov.	Amount	Fact.
47458822	1	A	1000	0
47458822	1	A	0	5
47452266	1	B	5000	0
123456	2	C	200	0
123456	2	C	0	1

We add the new column and get:

Code	Month	Prov.	Amount	Fact.	NewColumn
47458822	1	A	1000	0	5
47458822	1	A	0	5	1000
47452266	1	B	5000	0	0
123456	2	C	200	0	1
123456	2	C	0	1	200

Thanks!!

[smpa01 · Accepted solution]

@Anonymous  try this as a new column

X = 
IF (
    [Fact.] = 0
        && CALCULATE (
            MAX ( tx[Fact.] ),
            ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
        ) <> 0,
    CALCULATE (
        MAX ( tx[Fact.] ),
        ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
    ),
    IF (
        [Amount] = 0
            && CALCULATE (
                MAX ( tx[Fact.] ),
                ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
            ) <> 0,
        CALCULATE (
            MAX ( tx[Amount] ),
            ALLEXCEPT ( tx, tx[Code], tx[Month], tx[Prov.] )
        )
    )
)

breakdown with three 3 calculated columns

_maxfact = CALCULATE(MAX(tx[Fact.]),ALLEXCEPT(tx,tx[Code],tx[Month],tx[Prov.]))

_maxamt = CALCULATE(MAX(tx[Amount]),ALLEXCEPT(tx,tx[Code],tx[Month],tx[Prov.]))

New Column = Column = IF([Fact.]=0&&[_maxfact]<>0,[_maxfact], IF([Amount]=0&&[_maxfact]<>0,[_maxamt]))

[Anonymous]

Amazing answer!!

Thanks a lot!