Display $0.00 in place of NULL in dimension.

[pgloc2020]

Hello All,

I have created a simple table chart with the following columns-

In order to get the Credit and Debit amount separately, I used the following expression in dimension. 

=If(Match(FINANCIAL_TRANSACTION_TYPE, 'DA Credit'), AMOUNT) 

and

=If(Match(FINANCIAL_TRANSACTION_TYPE, 'DA Debit'), AMOUNT)

Now my report requirement is to display $0.00 where the amount is NULL (-)

Please let me know how I can achieve $ sigh before the amount and "0.00" where the amount is null . 

The original dataset looks like this-

Thank you so much in advance.

[ogster1974 · Accepted solution]

this will work.

=If(Match(Key,1,3,5,7,9), num(Value,'$#,##0.00'), num(0,'$#,##0.00'))

just add 0 to the else part of your If statement and format your numbers.

[pgloc2020]

Thank you so much. It works. 

[Clever_Anjos · Accepted solution]

You can try

num(COALESCE(If(Match(FINANCIAL_TRANSACTION_TYPE, 'DA Credit'), AMOUNT),0) ,'$#,##0.00'

as well

[pgloc2020]

Hi  Clever_Anjos,

This expression works too-

= num(COALESCE(If(Match(FINANCIAL_TRANSACTION_TYPE, 'DA Credit'), AMOUNT),0) ,'$#,##0.00')

Thanks a lot.