Formula field help: Deciding the output of a CASE or IF function

I have a formula in an app called [💸 Invoices] that calculates a [Total] (Formula) based on a [Direction] (Single Select) case, an [Amount] (Currency), and a [Tax] (Percent):

CASE([Direction],
  "⬆️ Expense", 
    -([Amount]+([Amount]*[Tax])), 
  "⬅️ Payable", 
    -([Amount]+([Amount]*[Tax])), 
  [Amount]+([Amount]*[Tax])
)

Then I have a formula that sums up the linked record Amount [Number] from the app called [📚 Ledger] so we know what has already been paid:

SUM([📚 Ledger].[Amount])

I have to find out the [Open] (Formula) balance by using a formula and I have tried the following:

IF([Total]-[Transaction]>0,[Total] - [Transaction],0)

But this formula returns the following error:

SUBSTRACT: Argument with type 'Total' NOT allowed.

I had to ask myself if I was missing something.

and I did some research

The CASE function seems to output a value "Decided by user".

In my case, it was returning a string result, but I cannot find how to decide the output.

Meanwhile, I was able to fix it quickly by replacing the CASE with an IF function:

IF([Direction]=="⬆️ Expense", 
  -([Amount]+([Amount]*[Tax])), 
    IF([Direction]=="⬅️ Payable", 
      -([Amount]+([Amount]*[Tax])), 
        [Amount]+([Amount]*[Tax]))
)

It is similar to the CASE one but returns a number instead of a string.

Question:

How can one decide the output of the function CASE or IF?

1 reply