Top N and Ranking by Multiple Categories and Multiple Measures

[ParinPBI123]

Hi All, 

I need some help regarding how to create a Top N for multiple fields and measures-- for the user to select. 

Fields: 

Customer Name

Event Name

Measure: 

Applied

Billed 

Net

I currently have a dynamic Top N built for Customer Name this can switch between Applied, Billed and Net measures. 

However i need to add Event Name into this selection. 

Current Steps taken: 

1) Manually create Table called "Table for Top N Selection"

Top N       Label for Top N
10             10

20             20

50             50

100           100

2) Created "KPIs Table"

KPI Names      Index

Applied           1

Billed               2

Net                  3

3) Create Measure:

Customer Name Rank Index =

SWITCH(

    TRUE(),

    [Selected KPIs]= "Applied",RANKX(ALL(table[CustomerName]),[Applied Amount Measure],,,Dense),

    [Selected KPIs]= "Billed",RANKX(ALL(table[CustomerName]),[Billed Amount Measure],,,Dense),

    [Selected KPIs]= "Net",RANKX(ALL(table[CustomerName]),[Net Amount Measure],,,Dense))

4) Create second Measure:

Customer Name Ranking for Top N =

     Var Select_Top_N_Number= SELECTEDVALUE('Table for Top N Selection'[Top N])

     Return

     IF([Customer Name Rank Index]<= Select_Top_N_Number,

     VALUE(1),

     VALUE(0))

Therefore using "Labell for top N" in one tile slicer

"KPI Names" in another slicer

Table with "Customer Name"     Applied Amount Measure    Billed Amount Measure    Net Aount Measure "Customer Name Rank Index"

Set "Customer Name Ranking for Top N" as Filter on table to Is 1 

I have a dynamic top N based for Customer Name and Interaction for this. However -- how would i add another catergory such as "Event Name" into this Model please?

Thank you,

Parin

[ParinPBI123 · Accepted solution]

I managed to find the solution: 

Rank Index =

IF(SELECTEDVALUE('cust or event'[cust or event Order]) = 0,

SWITCH(

    TRUE(),

    [Selected KPIs] = "Applied",RANKX(ALL(table[CustomerName]),[Applied Amount Measure],,,Dense),

    [Selected KPIs]= "Billed",RANKX(ALL(table[CustomerName]),[Billed Amount Measure],,,Dense),

    [Selected KPIs]= "Net",RANKX(ALL(table[CustomerName]),[Net Amount Measure],,,Dense))

,

SWITCH(

    TRUE(),

    [Selected KPIs] = "Applied",RANKX(ALL(table[STANDARD_EVENT_NAME]),[Applied Amount Measure],,,Dense),

    [Selected KPIs]= "Billed",RANKX(ALL(table[STANDARD_EVENT_NAME]),[Billed Amount Measure],,,Dense),

    [Selected KPIs]= "Net",RANKX(ALL(table[STANDARD_EVENT_NAME]),[Net Amount Measure],,,Dense)))

replacing customer rank index with this new Rank Index -- Top N with multple column and multiple measures worked fine.