cancel
Showing results for
Did you mean:
New Member

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

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!!

1 ACCEPTED SOLUTION
Memorable Member

@4DZiltoid  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]))
``````

New Animated Dashboard: Sales Calendar

2 REPLIES 2
Memorable Member

@4DZiltoid  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]))
``````

New Animated Dashboard: Sales Calendar

New Member

Thanks a lot!

Announcements