Aggregating calculation in columns in PowerPivot

minirhyder

Edit: new issue.

I'm trying to aggregate some data in PowerPivot in a calculated column. How the fuck do I do it?

Basically I have a list of user ID's in one table, and a list of transactions in another (there are various columns in this table, including user ID).
So table 1 looks like this:

user 1
user 2
user 3
user 4

table 2 looks like this:

user 1   transaction1
user 1   transaction2
user 2   transaction3
user 3   transaction4
user 3   transaction5
user 3   transaction6
user 3   transaction7

and so on. This is very simplified, of course. Basically the list of transactions contains multiple transactions for each user (if they did in fact make multiple transactions).

How do write a formula in table 1 so that it counts how many transactions each user made?
So what I'd like it to look like is:

user 1   2
user 2   1
user 3   4

and so on.


And yes I know I can do this all in SQL quite easily (and I have), but my task is to learn how to use PowerPivot, and so I'm doing all this stuff.

schuss

There should be a place to define relationships and select the tables. I hate the program though...

minirhyder

I meant before that, when you're first importing tables into PowerPivot.

I realized that it's just much easier to write a complex query joining a bunch of tables and importing the end result rather than doing it through PowerPivot.

Can be closed, I think.

minirhyder

Never mind, not closed.
New problem
OP updated.

MrTLicious

You should be able to do this with the SUMX function. What SUMX does is goes through a table and evaluates an expression for each entry, then sums them. If you combine this with an if statement that returns a 1 when the ID is the same as the ID in table 1 and 0 otherwise, you should be able to count the number of times that the ID repeats

In your table 1, where you can see the Add Column column, just click on the first entry and type something like the following:

=sumx(Table2,if(Table2[ID]=Table1[ID],1,0))

Where ID is the actual name of the ID column for each table, and the tables are numbered as in your example.

schuss

I think you can also port SQL into it, where you'd get select user, count(transaction) from usertable, transtable group by user

minirhyder

Yeah I started doing that. Doing the most work possible using SQL and then just doing clean up using PowerPivot.
Alternatively you can aggregate whatever you want using a pivot table, which is what PowerPivot intends you to do, I guess.

schuss

Yeah, powerpivot isn't really built for data conditioning