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.

rajulshah's avatar
rajulshah
Resident Rockstar
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.

jrhessey's avatar
jrhessey
Frequent Visitor
6 years ago

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