Calculated column looking up for specifc data

[jwi1]

Good day all,

Hope someone can help me with the following

As you can see, Box A is linked to 2 types of sales (W & P) and 2 customers (1 & 2)

I want to add a calculated column. 

The formula must look for de customer, based on sales type P

So, the combination 'Box A' and 'Sales W' should give 'Customer 2' as a result

Is this possible?

Thanks, John

Customer              Box          Sales       Column

1                            A             W            2

2                            A             P             2

1                            B             W            2 

2                            B             P             2

[Floriankx]

Hello,

try this:

=VAR Customer=Table[Customer]
VAR BOX=Table[Box]
VAR Sales="P"
Return CALCULATE(VALUES(Table[Customer]);Table[Box]=BOX;Table[Sales]=Sales;ALL(Table[Customer]))

[jwi1]

Hi Floriankx,

Seems to work fine!

Thanks a lot.

JOhn

[jwi1]

I have an additional question Florian,

Can you 'describe' the formula for me?

Because, if I read the formula, I do not really understand who it works.

Thanks,

John

[jwi1]

Good morning,

Unfortunately, the calculation is not working as good as I thought.

Can you please help me, so the info which I mentioned manually is automatically calculated?

Thanks!

John

[Ashish_Mathur]

Hi,

Do you want this as a calculated field for a calculated column?  Also, what is the logic there?  Is it that for a certain container_nr, if the term PTI & OFFH is found, then show the value in the adres_cod column corresponding to the term PTI & OFFH?  Pleae clarify.

[jwi1]

Good day,

In my table, a container_nr is mentioned several times. This is oke for me.

However, the container_nr can be mentioned with different adres_codes and different occ_act_groups. And that is what I don’t want😉

My goal is to see the container_nr mentioned in the table, but with only 1 adres_cod in the calculated column, not 2.

The proper adres_cod is always the one which is combined with the occ_act_group PTI & OFFH.

I hope this helps to give me an advise!

[v-yulgu-msft]

Hi @jwi1,

Please modify your formula as below:

Column =
VAR Customer = test[adres_cod]
VAR BOX = test[container_nr]
VAR Sales = "PTI&OFFH"
RETURN
    CALCULATE (
        VALUES ( test[adres_cod] ),
        test[container_nr] = BOX,
        test[occ_act_group] = Sales,
        ALL ( test )
    )

Best regards,

Yuliana Gu

[jwi1]

Good day all,

The formula works fine now, thanks for that!

I have one last challenge.

Below you see container_nr 'B' with 2 different adres_code (KRA588 and KRA001). with both, the occ_act_group is 'PTI & OFFH'.

The formula now gives me an error.

So, in this case, I want to mention the first adres_cod in the calculated column (in this case KRA588).

Does anyone have a solution for this?

Thanks upfront for your kind assistance in this!

John

[v-yulgu-msft · Accepted solution]

Hi @jwi1,

Column =
VAR Customer = test[adres_cod]
VAR BOX = test[container_nr]
VAR Sales = "PTI&OFFH"
RETURN
    CALCULATE (
        FIRSTNONBLANK ( test[adres_cod], 1 ),
        test[container_nr] = BOX,
        test[occ_act_group] = Sales,
        ALL ( test )
    )

Best regards,

Yuliana Gu

[Floriankx]

Hello,

your ALL Statement is wrong,

it has to be ALL(test(adres_cod)

with VAR you can store variables to call them in your function later, so we store Customer, Box and Sales for further use.

Then we apply these filter to the Calculate statement. VALUES gives me list of all adres_cod once, where the before mentioned filter is applied. The ALL Statement ensures to lookup all adres_cod because otherwise row content would try to apply the current row only. I think there are several good sites where ALL and VALUES are explained better than I can :).

Best regards.