Rank based on Measure for two tables

Hi Anonymous ,

Please create a new table:

Table = 
UNION (
    SELECTCOLUMNS (
        'Table1',
        "Department Group", 'Table1'[Department Group],
        "Product Key", 'Table1'[Product Key],
        "Date", 'Table1'[Date],
        "Cost", 'Table1'[Cost]
    ),
    SELECTCOLUMNS (
        'Table1',
        "Department Group",
            IF (
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
                    = BLANK (),
                'Table1'[Department Group],
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
            ),
        "Product Key", 'Table1'[Product Key],
        "Date2", "Period2",
        "Cost",
            IF (
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
                    = BLANK (),
                'Table1'[Cost],
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
            )
    )
)

You will get a table like this:

Create a rank column:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Department Group] = EARLIER ( 'Table'[Department Group] )
            && 'Table'[Date] = EARLIER ( 'Table'[Date] )
    ),
    'Table'[Cost],
    ,
    ASC
)

I think this is the result you want:

Best regards,

Yadong Fang

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

Hi Anonymous ,

I add a new product key in period 2 which does not exist in period 1:

Create a new table:

New_Table = 
var _tab = UNION (
    SELECTCOLUMNS (
        'Table2',
        "Department Group", 'Table2'[Department Group],
        "Product Key", 'Table2'[Product Key],
        "Date", 'Table2'[Date],
        "Cost", 'Table2'[Cost]
    ),
    SELECTCOLUMNS (
        'Table1',
        "Department Group",
            IF (
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
                    = BLANK (),
                'Table1'[Department Group],
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
            ),
        "Product Key", 'Table1'[Product Key],
        "Date2", "Period 2",
        "Cost",
            IF (
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
                    = BLANK (),
                'Table1'[Cost],
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
            )
    )
)
return
DISTINCT(_tab)

Create the other new table:

New_Table2 = 
UNION(
SELECTCOLUMNS (
        'Table1',
        "Department Group", 'Table1'[Department Group],
        "Product Key", 'Table1'[Product Key],
        "Date", 'Table1'[Date],
        "Cost", 'Table1'[Cost]
    ), 
    SELECTCOLUMNS (
        'New_Table',
        "Department Group", 'New_Table'[Department Group],
        "Product Key", 'New_Table'[Product Key],
        "Date", 'New_Table'[Date],
        "Cost", 'New_Table'[Cost]
    ))

Create a rank column:

Rank = 
RANKX (
    FILTER (
        'New_Table2',
        'New_Table2'[Department Group] = EARLIER ( 'New_Table2'[Department Group] )
            && 'New_Table2'[Date] = EARLIER ( 'New_Table2'[Date] )
    ),
    'New_Table2'[Cost],
    ,
    ASC
)

I think this is the result you want:

Best regards,

Yadong Fang

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