Add calculated column based on the matching values in other columns

[shiggs8]

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 any of those matching rows, put 'Bad Debt' in both of those rows for the 'Trans Type Calc', otherwise put 'Trans Type'

Store	Order No	Customer No	Amount	Bad Debt Amount	Date/Time	Trans Type	Trans Type Calc
22	54356	45	5	0	15/07/2021 08:00	Sale	Bad Debt
22	54356	45	0	6	15/07/2021 08:00	Cancelled	Bad Debt
3	13573	23	7	0	16/07/2021 08:00	Sale	Sale
4	37774	15	9	0	17/07/2021 08:00	Refund	Refund

Thanks in advance!

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[amitchandak · Accepted solution]

@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])