Forum Discussion

shiggs8's avatar
shiggs8
Frequent Visitor
4 years ago
Solved

Add calculated column based on the matching values in other columns

Hi, I am looking to create a calculated column based on the rows in 4 other columns. So what I want to do is if store no, order no, customer no and date are the same, and if 'Bad Debt Amount' >0 in a...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    shiggs8 , create new column like

     

     

    new column =
    var _sum = calculate(sum([Bad Debt Amount]), allexcept(Table, [store no],[order no],[customer no] ,[date]))
    return
    if(_sum >0 , "Bad Debt",[Trans Type])

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Hey shiggs8 ,

 

yes, that's possible.

You can first check for the maximum number for the same Store, Order, etc.

If this number is > 0 then you can return "Bad Debt" and otherwise the Trans Type. Try the following calculated column:

Trans Type Calc NEW =
VAR vBadDebt =
    CALCULATE(
        MAX( myTable[Bad Debt Amount] ),
        ALLEXCEPT(
            myTable,
            myTable[Store],
            myTable[Order No],
            myTable[Customer No],
            myTable[Date/Time]
        )
    )
RETURN
    IF(
        vBadDebt > 0,
        "Bad Debt",
        myTable[Trans Type]
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis