Sum, Group By and then Count

[Anonymous]

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.

[sunny_talwar · Accepted solution]

Try this

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

[Anonymous]

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

[sunny_talwar]

This

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

[Anonymous]

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.

[Anonymous]

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?

[sunny_talwar]

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