Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculated column looking up for specifc data

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 mu...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

 

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

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.