PowerPivot Tables

**amotto11** · 08-22-2011, 02:18 PM

I have a table called 'Car', in it i have a column called PolicyLink. the PolicyLink column contains numbers such as 445577, 445578, 445579, 445579, 445579. I am trying to associate how many cars are on a policy and what number that car will be given on the policy so I have a PolicyLink number and if it repeated more than once then the numbers will go in sequencial fasion until there is a new policy.

PolicyLink --------------------- Car Number

445577 --------------------- 1

445578 -------------------- 1

445579 --------------------- 1

445579 --------------------- 2

445579 --------------------- 3

here i have 3 policies so i have the first policy having one car, second having one car, and the third having three cars and so on and so forth. I am looking for a formula to insert into a new column in PowerPivot called 'car number'. the number under PolicyLink is PolicyLink and the -- just represent a change in column, so there is a column called 'car number' with single digits in it. Any help is appretiated

**royUK** · 08-22-2011, 02:29 PM

PowerPivot?

**amotto11** · 08-22-2011, 02:43 PM

yes this is in powerpivot, sorry i changed my original question because i was under the impression of one thing when i needed to be looking at another. any hel p is greatly appretiated

**romperstomper** · 08-23-2011, 04:59 AM

Do you have a numeric ID column in your Cars table? If so, you could use something like:

=COUNTROWS(filter('Cars','Cars'[PolicyLink]=earlier('Cars'[PolicyLink]) && 'Cars'[ID]<earlier('Cars'[ID])))+1

**amotto11** · 08-23-2011, 11:27 AM

is there any way to do it without a nemeric id column, if not, what would i need to do to create one?

**romperstomper** · 08-23-2011, 11:29 AM

In any well-structured database you should have a numeric ID field. Where is your data coming from originally?

**amotto11** · 08-23-2011, 03:12 PM

well i have multiple tables, each having a primary key of its own and each having foreign keys. My table 'Policy' has a PolicyLink that is its primary key(unique numbers). In my table 'Car', there is a primary column that is called CarID, and what i am using as my foreign is the PolicyLink column and I have a relationship through that with my table 'Policy'.

**romperstomper** · 08-23-2011, 03:59 PM

Then you can use CarID

**amotto11** · 08-23-2011, 04:16 PM

thank you!

**romperstomper** · 08-23-2011, 05:53 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save