Calculated Column

[Anonymous]

Hi All, 

I have a dataset with a Transactions Table, I want to create a caculated column that checks for a TransactionType using the TansactionType ID Column and then returns a value based off a calculation on the Amount Column.

For example:

If TransactionType is 1, Return amount in Amount Column multiplied by 2

If TransactionType is 2, Return amount in Amount Column.

If TransactionType is 15, Return amount in amount table minus 500.

So bascially, I want a Calculated Column thats checks for a transactionID, and returns a calulation on the Amount Column.

Any help would be appreciated.

[AntrikshSharma]

Try something like this.

Transaction Amount =
VAR TransactionID = Transaction[TansactionType ID]
VAR TransactionAmount = Transaction[Amount]
VAR ReturnAmount =
    SWITCH (
        TRUE (),
        TransactionID = 1, TransactionAmount * 2,
        TransactionID = 2, TransactionAmount,
        TransactionID = 15, TransactionAmount - 500,
        0
    )
RETURN
    Result

[Anonymous]

I tried this syntax:

Revenue =

VAR TransactionID = Transactions[TransactionTypeId]

VAR TransactionAmount = Transactions[Amount($)]

VAR ReturnAmount =

SWITCH(

TRUE(),

TransactionID = 1, TransactionAmount,

TransactionID = 2, 6 * 360.5,

TransactionID = 72, TransactionAmount,

TransactionID = 4, 0,

TransactionID = 5, TransactionAmount,

TransactionID = 12, 0,

TransactionID = 49, TransactionAmount * 0.65,

TransactionID = 66, TransactionAmount,

TransactionID = 68, TransactionAmount,

TransactionID = 69, TransactionAmount,

TransactionID = 67, TransactionAmount,

TransactionID = 35, TransactionAmount - 500,

0

)

RETURN

Result

But still returns an error message. Please what do i define inside of the RETURN function

[harshnathani]

HI @Anonymous ,

You code is okay.

You need to return Return Amount

Better Formatted Code

Revenue =
VAR TransactionID = Transactions[TransactionTypeId]
VAR TransactionAmount = Transactions[Amount($)]
VAR ReturnAmount =
    SWITCH (
        TRUE (),
        TransactionID = 1
            || TransactionID = 5
            || TransactionID = 72
            || TransactionID = 66
            || TransactionID = 68
            || TransactionID = 69
            || TransactionID = 67, TransactionAmount,
        TransactionID = 2, 6 * 360.5,
        TransactionID = 49, TransactionAmount * 0.65,
        TransactionID = 35, TransactionAmount - 500,
        0
    )
RETURN
    ReturnAmount

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)