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

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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      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's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

         

        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