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

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

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]))
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Floriankx,

 

Seems to work fine!

 

Thanks a lot.

 

JOhn

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

    • 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.