Forum Discussion
Row Number by table group
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!!
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.
3 Replies
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,
PaulHello 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.
