Forum Discussion

jrhessey's avatar
jrhessey
Frequent Visitor
6 years ago
Solved

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 ca...
Need Help
  • rajulshah's avatar
    rajulshah
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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