Top N values for each category after separating column

Hi Anonymous ,

Base data:

Output     result:

Try the below steps:

1.Add new column on the base table:

newcolumn = IF('Table'[Location]="Alaska"||'Table'[Location]="Florida","Type1","Type2")

rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Product] = EARLIER ( 'Table'[Product] )
            && 'Table'[Group] = EARLIER ( 'Table'[Group] )
            && 'Table'[newcolumn] = EARLIER ( 'Table'[newcolumn] )
    ),
    'Table'[Price],
    ,
    DESC,
    DENSE
)

Then create the below two new table:

Table2 = 
FILTER (
    SELECTCOLUMNS (
        'Table',
        "Product", CALCULATE ( VALUES ( 'Table'[Product] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2 ),
        "Group", CALCULATE ( VALUES ( 'Table'[Group] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2 ),
        "Location1", CALCULATE ( VALUES ( 'Table'[Location] ), 'Table'[newcolumn] = "Type1" &&'Table'[rank]<=2),
        "Price1", CALCULATE ( VALUES ( 'Table'[Price] ), 'Table'[newcolumn] = "Type1"&&'Table'[rank]<=2),
        "Rank", 'Table'[rank]
    ),
    NOT ( ISBLANK ( [Product] ) )
)

Table 3 =
FILTER (
    SELECTCOLUMNS (
        'Table',
        "Product", CALCULATE ( VALUES ( 'Table'[Product] ), 'Table'[newcolumn] = "Type2" ),
        "Group", CALCULATE ( VALUES ( 'Table'[Group] ), 'Table'[newcolumn] = "Type2" ),
        "Location2", CALCULATE ( VALUES ( 'Table'[Location] ), 'Table'[newcolumn] = "Type2" ),
        "Price2", CALCULATE ( VALUES ( 'Table'[Price] ), 'Table'[newcolumn] = "Type2" ),
        "rank", 'Table'[rank]
    ),
    NOT ( ISBLANK ( [Product] ) )
)

 Then add new column on table2：

location2 = LOOKUPVALUE('Table 3'[Location2],'Table 3'[Product],Table2[Product],'Table 3'[Group],Table2[Group],'Table 3'[rank],Table2[Rank])

price2 = LOOKUPVALUE('Table 3'[Price2],'Table 3'[Product],Table2[Product],'Table 3'[Group],Table2[Group],'Table 3'[rank],Table2[Rank])

Finally, adjust the order of table 2 below (I deleted the rank in the creation process, so that the table reported an error, and then re-entered the creation statement, the order was automatically adjusted).

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien