Count by a Sum

[#1]

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

[sunny_talwar]

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_])

[jagan · Accepted solution]

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.

[#4]

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_])

[sunny_talwar]

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_])

[#6]

Thanks that did it

[sunny_talwar]

No problem