Group by within an Expression

[#1]

Ok, there has to be an easy way to do this....

I have a data set containing person name, company name, and company revenue.  Since there are multiple people per company, the company revenue figures are repeated several times.  I need a report showing number of people, number of companies, and total revenue.  How do I set this up in a pivot table to avoid the double counting?

Thanks.

[onetwothree123]

draab,

Try using DISTINCT in your expression

[#3]

I know DISTINCT would work with the company names -- i.e., =count (distinct Company_Name)

But it's perfectly possible that there will be several companies reporting the same revenue, which I should think would cause problems.  Can you give an example of code that would work for the Revenue figures?

Thanks.

[#4]

could you upload an example of your data?

[#5]

Hi Draab,

If you do not have a revenue figure per person then if you include person as a dimension you will get the revenue repeated across that dimension.

Prehaps use comapny as the dimension, and then do a distinct count of people and a sum of revenue as expressions.

Thanks,

James

[swuehl · Accepted solution]

Something like

=sum( aggr(only(Revenue), Company))

should do then.

[#7]

That did it.  Thanks much.  Never saw 'only' before, but I'm very rusty.