Forum Discussion

ShmuelM's avatar
ShmuelM
Advocate II
9 years ago
Solved

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? 
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    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

     

     

MFelix's avatar
MFelix
Super User
4 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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