TopN for multiple column

[ngocnguyen]

Hi all

I have the input data as Table 1

I wanna create 2 ouput table as Table 2 & Table 3 with below logics:

- Getting Top N Category  by Qty ( A & C)

- For each categories (A,C) --> Getting Top 2 of Route by Qty  ( A: route X & Y, C: route X & H)

- Showing Only model & Qty of  Biggest Category & Route -> Result : Table 2 (Category: A, route x, y)

- Showing Only model & Qty of  2nd Biggest Category & Route -> Result : Table 3 (Category: C, route x, h)

Is there any way to create measure to get the above results?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

New Table 2 = 
VAR _RankCat =
    ADDCOLUMNS (
        DISTINCT ( Data[Cat] ),
        "@rank", RANKX ( DISTINCT ( Data[Cat] ), CALCULATE ( SUM ( Data[Qty] ) ),, DESC )
    )
VAR _toponecat =
    SUMMARIZE ( FILTER ( _RankCat, [@rank] = 1 ), Data[Cat] )
VAR _newtable =
    SUMMARIZE ( FILTER ( Data, Data[Cat] IN _toponecat ), Data[Route], Data[Qty] )
VAR _toptworoute =
    SUMMARIZE ( TOPN ( 2, _newtable, Data[Qty], DESC ), Data[Route] )
RETURN
    SUMMARIZE (
        FILTER ( Data, Data[Cat] IN _toponecat && Data[Route] IN _toptworoute ),
        Data[Model],
        Data[Qty]
    )

New Table 3 = 
VAR _RankCat =
    ADDCOLUMNS (
        DISTINCT ( Data[Cat] ),
        "@rank", RANKX ( DISTINCT ( Data[Cat] ), CALCULATE ( SUM ( Data[Qty] ) ),, DESC )
    )
VAR _toptwocat =
    SUMMARIZE ( FILTER ( _RankCat, [@rank] = 2 ), Data[Cat] )
VAR _newtable =
    SUMMARIZE ( FILTER ( Data, Data[Cat] IN _toptwocat ), Data[Route], Data[Qty] )
VAR _toptworoute =
    SUMMARIZE ( TOPN ( 2, _newtable, Data[Qty], DESC ), Data[Route] )
RETURN
    SUMMARIZE (
        FILTER ( Data, Data[Cat] IN _toptwocat && Data[Route] IN _toptworoute ),
        Data[Model],
        Data[Qty]
    )