Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jrhessey
Frequent Visitor

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] ) )
)

 

 

 

PowerBI.jpg

 

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

 

Excel.jpg

 

 

Thanks!!

1 ACCEPTED SOLUTION
rajulshah
Super User
Super User

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])

 

 

mtdaverage.png

 

Hope this helps.

View solution in original post

3 REPLIES 3
rajulshah
Super User
Super User

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])

 

 

mtdaverage.png

 

Hope this helps.

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
Community Support
Community Support

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.