Top 2 category

[ngocnguyen]

Hi

I have the input table as below. Is there any way to have the top 2 table ?

Logic for Top 2 Table:. Finding the top 2 category by Amt , then showing up the detail of category by Route

Thanks

[Greg_Deckler · Accepted solution]

@ngocnguyen I just called my source table Top 2 is all. See PBIX below sig. 

[CNENFRNL · Accepted solution]

[ngocnguyen]

Hi @CNENFRNL 

Highly appreciate your support. 

is there any way to get the Output as below?
Logic: get top 2 Amt by both CAT & Route

[Greg_Deckler]

@ngocnguyen Try:

Table 3 = 
    VAR __Table = SUMMARIZE('Top2',[CAT],"__Amt",SUM('Top2'[Amt]))
    VAR __Table1 = ADDCOLUMNS(__Table,"__Rank",RANKX(__Table,[__Amt]))
    VAR __Categories = DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[__Rank]<3),"__CAT",[CAT]))
RETURN
    FILTER('Top2',[CAT] IN __Categories)

[ngocnguyen]

@Greg_Deckler 

Many thanks for your support.

However, Top 2 table is the Output, but in your Dax formular, you calling the Top 2 table?, 

It 's a litte bit confusing. Could you please check again?

[Greg_Deckler · Accepted solution]

@ngocnguyen I just called my source table Top 2 is all. See PBIX below sig. 

[ngocnguyen]

@Greg_Deckler 

Thanks so much. So is there any way to get the Ouput by setting up measure. I wana the Ouput can appear in the Report tab in PBI 

[Greg_Deckler]

@ngocnguyen Sure, you could implement this as a Complex Selector. The Complex Selector - Microsoft Power BI Community