Count of rows uptil a certain row.

[floragray]

Hi All

I'm really new to powerpivot and in dire need of some help!!
In the table below i need to calculate an Average COlumn
and i need to divide the ''Total Number of Customers'' by the "Count (Batch Number)" uptil that row.

Ive tried every possible formula in Powerpivot but nothing seems to work
I would be really grateful if someone could help! Thankssss!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Batch Number[/TD]
[TD]Total Number of Customers[/TD]
[TD]Average(TO BE CALCULATED IN POWERPIVOT)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100
[/TD]
[TD]=100/1[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]200[/TD]
[TD]=200/2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]250[/TD]
[TD]=250/3(i.e count of rows uptil that row!)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]400[/TD]
[TD]=400/4[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]500[/TD]
[TD]=500/5[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]900[/TD]
[TD]=900/6[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1000[/TD]
[TD]=1000/7[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1500[/TD]
[TD]=1500/8[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MD610]

If your batch numbers are unique and you only to to count the rows by descending batch number then this should work as a calculated column for counting rows:

=CALCULATE(COUNTROWS(TableName), FILTER(TableName, TableName[Batch Number] <= EARLIER(TableName[Batch Number])))

 

[floragray]

Thanks a ton Mike!!
:D:D