topic Re: Fixed row numbers in Table like in Excel in DAX Commands and Tips

Fixed row numbers in Table like in Excel

Rapier — Sun, 04 Oct 2020 17:07:19 GMT

Hi All,

Is there any way to add a "fixed" row number column to tables visuals? It should be something similar like in Excel the index numbers of rows, also start from 1 and show for all rows a unique number, but changing the sort of rows hasn't effect on this.

I found many idea to add serial numbers, but all of these change the order of index column, when I sort some of columns.

Oh, and this tables contain summarized values, also it should be solved with DAX.

Re: Fixed row numbers in Table like in Excel

CNENFRNL — Sun, 04 Oct 2020 18:36:10 GMT

Rapier Sounds a bit weird, what's the point of its existence since you want them to keep static despite of sorting?

Re: Fixed row numbers in Table like in Excel

amitchandak — Mon, 05 Oct 2020 08:40:02 GMT

Rapier , In matrix , the first row and columns are fixed. No more options.

You can add an index column in Power Query/edit query/Transform Data

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Re: Fixed row numbers in Table like in Excel

Rapier — Mon, 05 Oct 2020 23:09:33 GMT

Good question CNENFRNL ...:\ But one of my clients would like to check tables like as in Excel...Scrolling down, and see what is the first 100 items. Of course I tried to advice to use TOPN filtering, but they insist on this kind of method. And biggest problem, that they have some kind of Tableau report, where it is possible to create a fixed serial number column (with function INDEX() maybe)

Also it would be great to solve on similar way here, and keep a single series of numbers in first column independent from any other columns.

amitchandak I know, that it's possible to create PQ index column, but as I said because of groups it is not usable here. With matrix the problem is, that rows and columns are filters on values, also it should be some kind of relationship between data, otherwise table will show the whole amounts in every rows or the first/last items in case of dimensions.

Re: Fixed row numbers in Table like in Excel

Anonymous — Mon, 13 Sep 2021 01:02:37 GMT

Rapier - Did you find a solution to your query yet?

CNENFRNL - The fixed row number requirement raised could be useful in a scenario where a user wants to sort a column (lets say = "Country") in a Table visual (having 4 column: Date, Country, Sales, Cumulative Sales) and yet want to see the cumulative SUM of Sales based on Ascending / Descending order of Country column alphabetically.

Next, in the same visual the user wants to sort the "Date" column and want to see the Cumulative SUM of Sales based on Ascending / Descending order of Date.

Rapierhas raised a good question here because other tools such as SAP Business Objects allows the user to sort the visual column and calculates the Cumulative SUM dynamically based on the sorted column.

Please let us know if anyone has come across same/similar requirement and if there is any tips or tricks available in PowerBI?