Forum Discussion
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 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.
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.