TOPN With Multiple Columns

[AlanP514]

Hai All ,
I want to Fetch TOP 10 Keywords Based on different Columns (Clicks, Cost,Impressions)
How it will be Done using DAX 

[Anonymous · Accepted solution]

Hi  @AlanP514 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Clicks_Rank =
RANKX(ALL('Table'),CALCULATE(SUM('Table'[Clicks])),,ASC)

Cost_Rank =
RANKX(ALL('Table'),CALCULATE(SUM('Table'[Cost])),,ASC)

impressions_Rank =
RANKX(ALL('Table'),CALCULATE(SUM('Table'[impressions])),,ASC)

2. Use Enter data to create a Slice Table.

3. Create measure.

Rank =
var _select=SELECTEDVALUE(Slice_Table[Slice])
return
SWITCH(
    TRUE(),
    _select="Click_Rank",[Clicks_Rank],
    _select="Cost_Rank",[Cost_Rank],
    _select="impressions_Rank",[impressions_Rank])

Flag =
IF(
    [Rank]<=10,1,0)

4. Use the [Slice] slicer of Slice_Table, and put [Flag] into Filter, and set is =1.

5. Result:

When Cost_Rank is selected, the top 10 rankings with cost are displayed.

When the selection is impressions_Rank, the top 10 ranking of impressions is displayed.

Best Regards,

Liu Yang

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

[AlanP514]

Thank you for the wonderful explanation

[amitchandak]

@AlanP514 , these are measures (of with sum) you need create 3 measures

Assuming you have measures

Clicks, Cost,Impressions

Top 10= calculate([Clicks], TOPN(10,allselected(Table[keywords]),[Clicks],DESC), values(Table[keywords]))

Top 10= calculate([Cost], TOPN(10,allselected(Table[keywords]),[Impressions],DESC), values(Table[keywords]))

Top 10= calculate([Impressions], TOPN(10,allselected(Table[keywords]),[Impressions],DESC), values(Table[keywords]))