Forum Discussion

vinothkumar1990

Helper II

2 years ago

Solved

Rownumber in virtual table

Hi All, I have created a virtual table using the below DAX function. I want to create a rownumber column based on the partition by YEAR, REGION, PRODUCT and Order by SALES (Could be strange for yo...

Need Help

Anonymous

2 years ago

Hi, vinothkumar1990
Based on your information, I create a table:

You can create a new measure and try the following DAX:

Vitrue Rank = 
VAR VirtualTable = 
SUMMARIZE(ALLSELECTED(Data),
    'Data'[YEAR],
    'Data'[REGION],
    'Data'[PRODUCT],
    'Data'[SALES],
    'Data'[PERIOD]
)

VAR ResultTable = 
ADDCOLUMNS(
    VirtualTable,
    "RowNumber", 
    RANKX(
        VirtualTable, 
        'Data'[SALES], 
        , 
        DESC, 
        Dense
    )
)

RETURN
MAXX(FILTER(ResultTable,SUM(Data[SALES])=[SALES]),[RowNumber])

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Rownumber in virtual table.pbix29 KB

Anonymous

Not applicable

2 years ago

Hi, vinothkumar1990
Based on your information, I create a table:

You can create a new measure and try the following DAX:

Vitrue Rank = 
VAR VirtualTable = 
SUMMARIZE(ALLSELECTED(Data),
    'Data'[YEAR],
    'Data'[REGION],
    'Data'[PRODUCT],
    'Data'[SALES],
    'Data'[PERIOD]
)

VAR ResultTable = 
ADDCOLUMNS(
    VirtualTable,
    "RowNumber", 
    RANKX(
        VirtualTable, 
        'Data'[SALES], 
        , 
        DESC, 
        Dense
    )
)

RETURN
MAXX(FILTER(ResultTable,SUM(Data[SALES])=[SALES]),[RowNumber])

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Rownumber in virtual table.pbix29 KB