Count of occurrences within PowerPivot

[grahamross87]

Hi,
I've been looking all over for an answer to what I thought would be a fairly straight forward question, but not been able to find anything.
To give a bit of context, I'm looking to add a column which allows me to filter my pivot table by customers that have booked 1, 2, 3 etc. times each year.
In order to do this, I'm looking to just get a count of the number of times the value in my 3rd column (CustID & BkngYr Merged) occurs. The output I'm hoping to get is in my "Count" column, but I can't find the correct formula to get to this.
Any help would be really appreciated.
Thanks

[TABLE="width: 336"]
<tbody>[TR]
[TD]CustomerID[/TD]
[TD]BookingYr[/TD]
[TD]CustID & BkingYr Merged[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]1000347[/TD]
[TD]Yr6[/TD]
[TD]1000347Yr6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1000347[/TD]
[TD]Yr6[/TD]
[TD]1000347Yr6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1000348[/TD]
[TD]Yr6[/TD]
[TD]1000348Yr6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1000348[/TD]
[TD]Yr6[/TD]
[TD]1000348Yr6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1000348[/TD]
[TD]Yr6[/TD]
[TD]1000348Yr6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1000348[/TD]
[TD]Yr6[/TD]
[TD]1000348Yr6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1000351[/TD]
[TD]Yr6[/TD]
[TD]1000351Yr6[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

For a calculated column, something like this:

=CALCULATE(counta(Table1[CustomerID]),Table1[CustID & BkingYr Merged]=earlier(Table1[CustID & BkingYr Merged]))

should work.

 

[grahamross87]

I've tried this, but it's just returning a value of "1" for all the rows. Any ideas?

 

[RoryA]

That's odd as it works for me with your sample data, returning exactly the values you show. Can you put a workbook somewhere for review? (SkyDrive/Dropbox/etc)

 

[grahamross87]

Yes, give me a minute. I'm using 2010 by the way - would this make any difference?

 

[RoryA]

It shouldn't - that's what I tested with. Which version of PP was it?

Edit: I've put my sample file on SkyDrive here: http://1drv.ms/1iik1qN

 

[grahamross87]

https://www.dropbox.com/s/x1l8bqj48yhrv3g/Count%20PP%20Sample%20Records.xlsx
I've uploaded the file I'm using here. I can't access the PP window in your version as I'm on a different version (10.50.4000.0)

 

[RoryA]

It's definitely a version issue. I opened your file, upgraded the PP data, copied your formula, deleted the column you had added and added a new one pasting in the same formula, and it calculated as you want it to. Unfortunately, I don't know offhand how to make it work in the old version of PP - I presume you can't upgrade?

 

[grahamross87]

Ok that would make sense. I'll get onto our IT bods and see if they can upgrade my version.
Really appreciate your help, thanks!

 

[RoryA]

Good luck!

Glad to help - and welcome to the forum, by the way.