Forum Discussion

ParinPBI123's avatar
ParinPBI123
Frequent Visitor
2 years ago
Solved

Top N and Ranking by Multiple Categories and Multiple Measures

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

 

 

Need Help
  • ParinPBI123's avatar
    ParinPBI123
    2 years ago

    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. 

1 Reply

  • ParinPBI123's avatar
    ParinPBI123
    Frequent Visitor
    2 years ago

    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.