cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Adding index number or row number in a summarized table

Hello!

 

I'm creating a summarized table and I need to have an index number or row number. Search results lead to having index number using the Power Query but in my case, I need to have a summarized table.

My summarized table is just like this:

CategoryIndex (I don't know how to have this)
A 
B 
C 
D 
E 

 

I tried this but I only get the same result:

crln-blue_0-1607632612946.png

 

Any help is appreciated. Thank you!

1 ACCEPTED SOLUTION
Super User IV
Super User IV

Please try this column expression.

 

CategoryIndex = RANKX(Category, Category[Category],,ASC)
 
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
Super User IV
Super User IV

This would be done more easily in the query editor, but here is how to do it in a DAX table.  There probably is a way to do it in one table variable, but I used two to make it easier.  Replace with your actual table/column names.

 

New Table =
VAR table1 =
    ADDCOLUMNS (
        SUMMARIZE (
            Category,
            Category[Category],
            Category[Group]
        ),
        "merge",
            Category[Group] & Category[Category]
    )
VAR table2 =
    ADDCOLUMNS (
        SUMMARIZE (
            Category,
            Category[Category],
            Category[Group]
        ),
        "Rank",
            VAR thismerge = Category[Group] & Category[Category]
            RETURN
                COUNTROWS (
                    FILTER (
                        table1,
                        [merge] < thismerge
                    )
                ) + 1
    )
RETURN
    table2





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello! How do you this in a DAX column? Thank you! I'm gonna use this for my hierarchy. Much thanks again!

Super User IV
Super User IV

Not sure what you mean by summarized table.  Are you making a DAX table with SUMMARIZE?  Or do you mean a table visual with the Category and Group columns?  Please clarify.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello @mahoneypat !
Yes, I'm making a DAX table using the SUMMARIZE function. The above excel screenshot that I sent is my expected outcome from my DAX table. Thanks!

Super User IV
Super User IV

Please try this column expression.

 

CategoryIndex = RANKX(Category, Category[Category],,ASC)
 
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Btw @mahoneypat , how do I add this during the summarized formulas? Also, can I add index numbers based on group?
For example: (for some reason, I can't attach a table so I provided a sample screeshot)

crln-blue_1-1607670849112.png

Thanks in advance!

Thank you!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors