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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ShmuelM
Advocate I
Advocate I

How to add a Serial Row Number Column in Power BI Table / Matrix visual?

Hi is there a way to do that , just to add the row number that will regenerate every time the table changes? 

2017-07-10 13_36_44-First Report - Power BI Desktop.png

1 ACCEPTED SOLUTION

Hi @ShmuelM,

 

Grabbing in your table just add a measure with the countrows:

 

Row_Number = 
CALCULATE (
    COUNTROWS(Profit_Table),
    FILTER ( ALLSELECTED ( Profit_Table ), Profit_Table[Position] <= MAX ( Profit_Table[Position]) )
)

As you can see in the print below it counts the number of rows no matter if you have slicer or not, left table is the full data, right table is influenced by slicer.

 

count_rows.png

 

Please be aware that this measure is influenced by the order of your Position so if you want to order by another column this will give you the row number in a different order also.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

28 REPLIES 28
plansis
Regular Visitor

there should be a simple feature to add a row number in the #matrix #powerbi

row number is not a rank but , similar to Excel, just a row number. 

No sorting should change that. 

sbvsv
Frequent Visitor

https://community.powerbi.com/t5/Desktop/How-to-add-a-Serial-Number-or-Row-Number-Column-in-Power-BI...

 

@ShmuelM just make sure before adding the index/row number (explained in the post above) you order your dataset by the reference column (in your case Position)

Hi @sbvsv , 

Thanks for answering ,

I saw this solution but this is not answering my question . 

for example I have this this table, 2 columns :ID , CITY in the phisic table: 

ID  , CITY

1   WASHINGTON

2   AMSTERDAM

3   LONDON

4   ROMA

 

IF I will select and filter in the report only 3 cities : WASHINGTON , AMSTERDAM  , ROMA 

The Table In PowerBI will show me 

1 WASHINGTON

2 AMSTERDAM

4 ROMA

 

I want to see it like this: 

1 WASHINGTON

2 AMSTERDAM

ROMA . 

 

I don't want the 'ID' Of the row , just a running number in the display table in Power BI that will change every time I chage filter / order. 

I hope the question is clearer.

Hi @ShmuelM,

 

Grabbing in your table just add a measure with the countrows:

 

Row_Number = 
CALCULATE (
    COUNTROWS(Profit_Table),
    FILTER ( ALLSELECTED ( Profit_Table ), Profit_Table[Position] <= MAX ( Profit_Table[Position]) )
)

As you can see in the print below it counts the number of rows no matter if you have slicer or not, left table is the full data, right table is influenced by slicer.

 

count_rows.png

 

Please be aware that this measure is influenced by the order of your Position so if you want to order by another column this will give you the row number in a different order also.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



marksn
Frequent Visitor

What about if you have a table visual comprising of columns from multiple table?

Hi @marksn ,

 

In this case you need to make your based on a virtual table created on that specif calculation, that is based on your selections.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



marksn
Frequent Visitor

Hi Miguel,

Thanks for the response. Do you know of an easy way to create a virtual table based on a table visual with multiple table & measure as source of columns? I reckon it can be done by copying the Performance Analyzer Query?

Anonymous
Not applicable

This doesn't seem to work with a matrix...any clue as to why?

Hi @Anonymous ,

 

This depends on the context can you share some mockup data?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Sure! 
So I have the following Matrix: 

eugeniaCastilla_0-1647347020954.png

I need to add one row, at the beggining of the matrix, with the row number: 1,2,3,4,5,6 .....
I have tried it your way and it just gives random numbers that i can't include in the matri. The closest I have been to achieving my goal was this expression: 

Row_Number =
RANKX(ALL(v_inf_ttv_alertas_campanas_dia[ds_campania]),[Sumatorio])
 
However, I can only add the column as a value, wich obviously doesn't do what I want. 

Hi @Anonymous ,

 

Has you refer you cannot add a measure except on the values part of your matrix, meaning that you cannot use it in the rows.

 

In order to do this maybe you can use a disconnected table with the order and then make a measure using the ranking and the disconnected table, one question is your ranking based on the total value of the matrix? so the last column that refers to the total GRP20?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi again hehe

I am not sure of what you mean...I am quite new to Power Bi... I do can tell you that in fact, the table has to be ordered by that column, GRP20, so I guess the ranking should be done having that under consideration? I dont know x) I am so lost here... 

Hi @Anonymous ,

 

For this you need to do the following:

  • Add a disconnected table with the values of ranking 1 to 20 (or more if you need) this can be added using an what if parameter
  • Then create the following measures:
Ranking sales = CALCULATE( RANKX(ALL( Table[Column of the Rows in matrix]), calculate([Measure])), ,desc), ALLSELECTED(Table[Column of the Columns in matrix]))

 

Values for matrix = IF(HASONEVALUE(Table[Column of rows in matrix]),  IF(SELECTEDVALUE(ranking[ranking]) = [Ranking sales], [Measure]), [Measure])

 

  • Now setup your matrix in the following way:
    • Rows:
      • Ranking
      • Column you need
    • Columns:
      • Column you need
    • Values:
      • Measure Values for matrix
    • Turn of step layout and drill down to the lowest level

Has you can see below the matrix on top that has the ranking is matching the one on the bottom that is only sorted by the total values:

 

MFelix_0-1647448241387.png

The data is from the Adventure works database.

 

If you need any PBIX file with example please tell me and I can try and share one.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Good Day, Thank you very much
It is really helpful, Could you please provide this PBIX file.

Hi @Rana_Rumeel,

 

I don't have the PBIX file it was done some time ago.

 

Sorry


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you for your response.
Dynamic Row Count basically I am seeking for some help here with some sort of PBIX.

This solution works so thank you for that but I'm surprised how something so simple requires additional table and measures.

This should be a simple switch in the matrix settings "Add row numbers".

Microsoft is too caught up in adding flashy visuals and they forget the basic things

Anonymous
Not applicable

Hi again @MFelix , that pbix file sounds great hahaha, could you please share?

Hi @Anonymous

 

See file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

thanks!!! got it to work some other wy but I will for sure keep this example for future references!!! Again, thank you so so much!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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