Forum Discussion

ngocnguyen's avatar
ngocnguyen
Helper IV
3 years ago
Solved

TopN for multiple column

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 T...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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]
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

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]
    )