Get only the payment amount from the same table

[newkingdom]

I have a basic table with a sales Id, Amount, Type activity. I want to create a new column in the table which will just return the Payments. So type = Payment, otherwise it will return a 0 for the other types. It should be based on id and the result set should look like below.

Id	Amount	Type		Payment AMount
1	2	Payment		2
1	3	Refun		0
1	6	Payment		6
2	4	Payment		4
2	4	Refund		0
2	6	Refund		0
3	6	Something		0
3	4	Refund		0
3	1	Payment		1
4	1	Payment		1
4	2	Something		0
4	5	Something		0
4	10	Payment		10

[hmahesh94 · Accepted solution]

Hi @newkingdom 

You can create a calculated column in this case:

Payment Amount  = IF(
                                      TableName[Type] = "Payment",

                                       TableName[Amount], 0)

I just tested this on my data and it worked. If the data-type of Amount is Number use 0, otherwise use "0"

If you think my solution worked , please 'Accept' it as a solution and give it a like.

Thanks,

Mahesh

[mh2587]

[jppv20]

Hi @newkingdom ,

Try this:

Payment_Amount = IF('Table'[Type]="Payment",'Table'[Amount],0)

If I answered your question, please mark it as a solution to help other members find it more quickly.