Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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.

1 Solution

Accepted Solutions
MVP

Try this

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

6 Replies
MVP

Try this

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

Anonymous
Not applicable
Author

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

MVP

This

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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?

MVP

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