Rank for Summarize Table

[ak77]

Hi All, 

Need a help i need to include a column in my summarize table which basically gives Rank/Index/Serial No. (1,2,3....)  when displayed. Below is the summary Table. Can anyone please help

SUMMARIZE(
filter(ODATA_LINKS, ODATA_LINKS[SourceType] <> BLANK() ),
ODATA_LINKS[SourceWorkItemId],ODATA_LINKS[Complete_ChildCount],ODATA_LINKS[InterfaceNumber],ODATA_LINKS[team],
ODATA_LINKS[Parent],
ODATA_LINKS[SourceTitle],ODATA_LINKS[ExtractedTitle],ODATA_LINKS[CalsourceEndDate],ODATA_LINKS[CalsourceLikely],ODATA_LINKS[CalsourceProb],ODATA_LINKS[Total_Count],
ODATA_LINKS[sourceEndDate],
ODATA_LINKS[sourceLikley],
ODATA_LINKS[sourceProb],
ODATA_LINKS[SourceOutcome],ODATA_LINKS[Milestone_Count],ODATA_LINKS[NotStarted_ChildCount]

)

[MNedix · Accepted solution]

Heya,

You could simply add a calculated column in your virtual table, as below:

....
ODATA_LINKS[SourceOutcome],ODATA_LINKS[Milestone_Count],ODATA_LINKS[NotStarted_ChildCount]
"Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
)

This will create an index column based on the ODATA_LINKS[Parent] column. You can change this based on needs.

PS: I would start the formula with FILTER first - like this:

Virtual table = 
FILTER(
SUMMARIZE(
ODATA_LINKS, ODATA_LINKS[SourceType], ODATA_LINKS[SourceWorkItemId], ODATA_LINKS[Complete_ChildCount], ODATA_LINKS[InterfaceNumber], ODATA_LINKS[team],
ODATA_LINKS[Parent], ODATA_LINKS[SourceTitle], ODATA_LINKS[ExtractedTitle], ODATA_LINKS[CalsourceEndDate], ODATA_LINKS[CalsourceLikely], ODATA_LINKS[CalsourceProb], ODATA_LINKS[Total_Count], ODATA_LINKS[sourceEndDate], ODATA_LINKS[sourceLikley],
ODATA_LINKS[sourceProb], ODATA_LINKS[SourceOutcome], ODATA_LINKS[Milestone_Count], ODATA_LINKS[NotStarted_ChildCount], "Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
ODATA_LINKS[SourceType] <> BLANK()
)

Hope it helps.

[ak77]

Thanks @Jihwan_Kim and @MNedix  for ur efforts.

Both solution worked. ! Closing this issue

[Jihwan_Kim · Accepted solution]

Hi, 

I am not sure how your expected outcome looks like, but I tried to craete a sample pbix file like below.

It is for creating a new table with adding one more column that shows Row Number.

The row number is based on more than one column.

ROWNUMBER function (DAX) - DAX | Microsoft Learn

New table with additional column = 
VAR _t =
    SUMMARIZE (
        ODATA_LINKS,
        ODATA_LINKS[SourceType],
        ODATA_LINKS[InterfaceNumber],
        ODATA_LINKS[Parent],
        ODATA_LINKS[ExtractedTitle],
        ODATA_LINKS[NotStarted_ChildCount],
        ODATA_LINKS[Milestone_Count],
        ODATA_LINKS[Complete_ChildCount],
        ODATA_LINKS[Total_Count]
    )
VAR _addrownumber =
    ADDCOLUMNS (
        _t,
        "RowNumber",
            ROWNUMBER (
                _t,
                ORDERBY (
                    ODATA_LINKS[NotStarted_ChildCount], DESC,
                    ODATA_LINKS[Milestone_Count], DESC,
                    ODATA_LINKS[Complete_ChildCount], DESC,
                    ODATA_LINKS[Total_Count], DESC
                )
            )
    )
RETURN
    _addrownumber

[MNedix · Accepted solution]

Heya,

You could simply add a calculated column in your virtual table, as below:

....
ODATA_LINKS[SourceOutcome],ODATA_LINKS[Milestone_Count],ODATA_LINKS[NotStarted_ChildCount]
"Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
)

This will create an index column based on the ODATA_LINKS[Parent] column. You can change this based on needs.

PS: I would start the formula with FILTER first - like this:

Virtual table = 
FILTER(
SUMMARIZE(
ODATA_LINKS, ODATA_LINKS[SourceType], ODATA_LINKS[SourceWorkItemId], ODATA_LINKS[Complete_ChildCount], ODATA_LINKS[InterfaceNumber], ODATA_LINKS[team],
ODATA_LINKS[Parent], ODATA_LINKS[SourceTitle], ODATA_LINKS[ExtractedTitle], ODATA_LINKS[CalsourceEndDate], ODATA_LINKS[CalsourceLikely], ODATA_LINKS[CalsourceProb], ODATA_LINKS[Total_Count], ODATA_LINKS[sourceEndDate], ODATA_LINKS[sourceLikley],
ODATA_LINKS[sourceProb], ODATA_LINKS[SourceOutcome], ODATA_LINKS[Milestone_Count], ODATA_LINKS[NotStarted_ChildCount], "Index", RANKX(ALL(ODATA_LINKS),ODATA_LINKS[Parent],,ASC)
ODATA_LINKS[SourceType] <> BLANK()
)

Hope it helps.