Distinct count

[MT10]

can someone advise me a formulae for below case, i want to count the  no.of invoices with invoice amount <>0

case(

    Invoice Amount > 0,     distinct(

        Invoice Number), 

        '0'

    )

)

[RADSr · Accepted solution]

Is your data at the grain of invoice number?   If so, then you can use:

sum(case(inv_amt <> 0, 1, 0 ) )

If it's not ( e.g. your reporting is at the invoice line level ) then you want something like:

sum(case(inv_amt <> 0, 1, 0 ), groupby(inv_number) )

If you are doing a KPI insight then just distinct(inv_no)  and then filter out amounts = 0 

HTH - if it doesn't get you there you can reply w/ more detailed info about your use case and we'll get it.