Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Creating Calculated Tables For 3 Different Conditions

 

Condition 2: If Sales Group from Table 1 is IC then calculated column will be IC.

 

Condition 3: From Table 1, if Sales Group is not IC and Type2 is COGS then copy Data from New Consol in the calculated column

 

//tksnota

Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    1 year ago

    Hi Anonymous 

     

    Try this:

    CalculatedColumn = 
IF(
    Table1[SalesGroup] = "IC", 
    "IC",  -- Condition 2
    IF(
        Table1[Type2] = "Sales", 
        LOOKUPVALUE(Table2[ItemCode], Table2[SalesGroup], Table1[SalesGroup]),  -- Condition 1
        IF(
            Table1[Type2] = "COGS", 
            FORMAT(Table1[New Consol], "NA"),  -- Condition 3
            BLANK()  
        )
    )
)

     

    Best,
    Muhammad Yousaf

     

    If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

     

    LinkedIn

3 Replies

  • muhammad_786_1's avatar
    muhammad_786_1
    Solution Supplier
    1 year ago

    Hi Anonymous 

     

    Try this:

    CalculatedColumn = 
IF(
    Table1[SalesGroup] = "IC", 
    "IC",  -- Condition 2
    IF(
        Table1[Type2] = "Sales", 
        LOOKUPVALUE(Table2[ItemCode], Table2[SalesGroup], Table1[SalesGroup]),  -- Condition 1
        IF(
            Table1[Type2] = "COGS", 
            FORMAT(Table1[New Consol], "NA"),  -- Condition 3
            BLANK()  
        )
    )
)

     

    Best,
    Muhammad Yousaf

     

    If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

     

    LinkedIn

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share data in a format that can be pasted in an MS Excel file.  Show the expected result there.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous, hello Ashish_Mathur and muhammad_786_1, thank you for your prompt reply!

     

    Please try as follows:

     

    CalculatedColumn = 
SWITCH(
    TRUE(),
    
    -- condition2
    Table1[SalesGroup] = "IC", 
    "IC",
    
    -- condition1:  SalesGroup <> IC and Type2 =Sales， use GLAccount, Costcenter, CostUnit and Project from Table2 to search ItemCode
    Table1[Type2] = "Sales" && Table1[SalesGroup] <> "IC",
    LOOKUPVALUE(
        Table2[ItemCode], 
        Table2[GLAccount], Table1[GL_Cod], 
        Table2[Costcenter], Table1[Cost_Center], 
        Table2[CostUnit],Table1[Profit_Center],
        Table2[Project], Table1[Project]
    ),
    
    -- condition3:  SalesGroup <> IC and Type2 = COGS，return New Consol 
    Table1[Type2] = "COGS" && Table1[SalesGroup] <> "IC",
    Table1[New Consol],
    
    -- default
    BLANK()
)

     Result:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.