Row Number by table group

[jrhessey]

I'm trying to get a day count inside of a grouped by sum table.  I've found some posts here that I think give me a row number in the whole table, not the distinct rows in the summed table.  As you can see in the picture below RowNum appears to be counting up, but I'd just like 1,2,3,4 so I can divide the the Invoiced column by the number and get the running average invoice for through the month (see the excel sheet). 137360 / 1 = 137360, 210689 / 2 = 105344, and so on down the sheet/table. 

Formula I'm using 

RowNum =
CALCULATE (
COUNT ( DSO_All[Index] ),
FILTER ( ALLSELECTED ( DSO_All ), DSO_All[Index] <= MAX ( DSO_All[Index] ) )
)

Average daily invoice total I'm trying to achieve is below

Thanks!!

[rajulshah · Accepted solution]

Hello @jrhessey,

You can create a measure as below:

Index = RANKX(ALLSELECTED(RowNum[RunDate]),CALCULATE(FIRSTDATE(RowNum[RunDate])),,ASC,Dense)

And then the below DAX query will help you:

Average Daily Invoice = 
VAR MTDInvoice = TOTALMTD(SUM(RowNum[Daily Invoice]),RowNum[RunDate])
RETURN DIVIDE(MTDInvoice,[Index])

Hope this helps.

[jrhessey]

Thanks for both answers @rajulshah and @V-pazhen-msft.  I gave the answer to @rajulshah because of the row numbers and gave kudos to @V-pazhen-msft , but if anyone is reading this and doesn't need that, both answers will work for you.  Thanks!!!

[V-pazhen-msft]

Hi, @jrhessey 

You don’t necessarily need the row number, you can filter with the date column instead.

Net Invoiced MTD = 
CALCULATE(SUM('Table'[Daily Invoice]),FILTER('Table','Table'[RunDate]<=EARLIER('Table'[RunDate])))

Average Daily Invoice = 
CALCULATE(AVERAGE('Table'[Daily Invoice]),FILTER('Table','Table'[RunDate]<=EARLIER('Table'[RunDate])))

If you need an index column and rename it as Day, it is suggested to use add index function in the power query.

Best,
Paul