6 Replies Latest reply: Feb 24, 2016 1:57 PM by Sunny Talwar

# Count by a Sum

I am trying to count retailers up who are greater than 250000 in net sales but I’m not sure how

Count({<...Where net sales is greater than 250,000>}[Customer No_])

The "Net Sales" is a calculation that is listed below

Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])

Can I Count the results of a "SUM"

Thanks

• ###### Re: Count by a Sum

May be this:

Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 250000"}>}[Customer No_])

• ###### Re: Count by a Sum

Hi,

Try like this using set analysis

=Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 250000"}>} DISTINCT [Customer No_])

OR you can also try like this using Aggr() like below

=Sum(Aggr(If(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])  > 250000, 1, 0), [Customer No_]))

Regards,

Jagan.

• ###### Re: Count by a Sum

Thank you this worked for what I was trying to do

Additional question if I wanted to add a RANGE. example customers between 150,000 and 250,000. How would I do that?

Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) < 250000"}>} DISTINCT [Customer No_])

and

Count({<[Customer No_] = {"=(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 150000"}>} DISTINCT [Customer No_])

• ###### Re: Count by a Sum

Try this:

Count({<[Customer No_] =

{"=

(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) > 150000

and

(Sum({<[Invoice Line.Type] = {2}>}[Invoice Line.Amount Including VAT]) - Sum({<[Credit Memo Line.Type] = {2} >}[Credit Memo Line.Amount Including VAT])) < 250000

"}>}[Customer No_])

• ###### Re: Count by a Sum

Thanks that did it

• ###### Re: Count by a Sum

No problem