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

[ShmuelM]

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

[plansis]

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]

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)

[ShmuelM]

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

3 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.

[MFelix · 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.

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

[marksn]

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

[MFelix]

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.

[marksn]

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]

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

[MFelix]

Hi @Anonymous ,

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

[Anonymous]

Sure! 
So I have the following Matrix: 

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. 

[MFelix]

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?

[Anonymous]

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... 

[MFelix]

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:

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.

[Rana_Rumeel]

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

[MFelix]

Hi @Rana_Rumeel,

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

Sorry

[Rana_Rumeel]

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

[fedlov]

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]

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

[MFelix]

Hi @Anonymous

See file attach.

[Anonymous]

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!