Help with Invoice equal to zero

[educastri83]

Hello community!!

I am currently working on a dashboard that allows me to identify invoices that are equal to zero.
I have noticed the existence of some charges in the invoices that not in all cases is correct to be billed as "shipping cost invoice" and "delinquency cost". Then I should be able to calculate two KPI'S:

1) when the invoices are equal to zero.
2) and when the sum of "Total Invoive Charge" in field
  INVOICE_NUM is equal to zero, assuming there are no charges: "shipping cost invoice" and "delinquency cost"

Point 2) will help me establish more precisely the service charges that really are zero. 

 The previous example shows a case that when adding the threes charges of the invoice the total is 8, but really the charge is zero, since it includes the items not to be considered for the analysis.

The third requirement is not a KPI, but I would like to be able to write a field from the script in order to have the "Total Amount of the Invoice". Currently the data brings the total but at the level of charge.

Finally, I have included a ready list related to point 1) and allow filtering the cases with invoices equal to zero, with the following expression: = Aggr (if (sum ([Total Invoice Charge]) = 0, 'Invoice Equal to zero ',' N / A '), [Invoice-Num]). I would like those cases only to be in a pivot chart (try it but it shows me the rest of the cases) and also in a bar chart.

Thanks again, counting on your usual help

[educastri83]

The result is ok. Thanks @sunny_talwar 

Only a couple of final things to solve based on the previous expression:

1. How can I exclude cases with a charge with zero value? I explain it better, suppose the charges:
'Promotional three months free' and 'Promotional free 3 months' are equal to zero, I would like to exclude those cases. 

Although the amount is repeated and the charges are different, when they are equal to zero they have no risk

2. How can I count the cases in a KPI using a text box?

[sunny_talwar · Accepted solution]

I think my expression is already excluding when the amount  = 0... I reloaded with the new file attached

.

For a distinct count of Invoice Number, you can try this

=Count(DISTINCT {<[Invoice-Num] = {"=Count(DISTINCT [Charge Details]) > 1 and Max(Aggr(Count({<Taxed_Charge = {[<>0]}>}Taxed_Charge), [Invoice-Num], Taxed_Charge)) > 1 "}>} [Invoice-Num])

For overall count

=Count({<[Invoice-Num] = {"=Count(DISTINCT [Charge Details]) > 1 and Max(Aggr(Count({<Taxed_Charge = {[<>0]}>}Taxed_Charge), [Invoice-Num], Taxed_Charge)) > 1 "}>} [Invoice-Num])

[educastri83]

It works very good!!

thanks for all @sunny_talwar