6 Replies Latest reply: Jul 26, 2018 9:04 PM by Sunny Talwar

# Sum, Group By and then Count

I have a raw data table below which has multiple lines of same orders and revenue associated to it. I first want to Sum all the revenue based on the Orders and then want to count all the orders that are above says \$500 I came up with below expression but doest seem to be working

COUNT ({<Aggr(SUM(Revenue,[Order No])={">500"}>}[Order No])

Below is the Example

 Order No Revenue 3703 \$     500.00 3703 \$       50.00 3703 \$       56.00 3703 \$       20.00 3805 \$     460.00 3805 \$ 8,000.00 3805 \$       23.00 3805 \$     210.00 3806 \$     499.00 3811 \$       20.00 3811 \$     100.00 3811 \$     213.00

First I need to sum up all the Revenue by their Orders

This will Eventually look like this

 Order No Revenue 3703 \$     626.00 3805 \$ 8,693.00 3806 \$     499.00 3811 \$     333.00

And the The orders Above \$500 = 2

How do I get these 2 steps in one expression? I tried the one above but doesn’t work.

• ###### Re: Sum, Group By and then Count

Try this

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) > 500"}>} [Order No])

• ###### Re: Sum, Group By and then Count

Thanks Man! That worked. another sub question on that If i need get a count of Revenue in between 200-500 should i use the below syntax

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) <500"} and {"=Sum(Revenue) >200"} >} [Order No])

• ###### Re: Sum, Group By and then Count

This

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) >200 and Sum(Revenue) <500"}>} [Order No])

• ###### Re: Sum, Group By and then Count

That worked too, Thanks Again!

Since I'm new I still struggle with set analysis and especially with expressions that needs two or three steps, Do you have any recommendation on books or sites, that will help me get this basics right.

• ###### Re: Sum, Group By and then Count

Sunny,

I had another Question, Eventually I'm trying to create a Histogram that shows orders, >100, between 100-200, 200-500 and >500. whats the best way to do it ..Do I create four separate expressions?

• ###### Re: Sum, Group By and then Count

Created a calculated dimension like this

Calculated Dimension

Aggr(

If(Sum(Revenue) < 100, Dual('<100', 1),

If(Sum(Revenue) <  200, Dual('100-200', 2),

If(Sum(Revenue) < 500, Dual('200-500', 3), Dual('>500', 4)))), [Order No])

Expression

Count(DISTINCT [Order No])