Forum Discussion

JohnYU's avatar
JohnYU
Frequent Visitor
2 years ago

top n by dynamic

Dear experts,

 I have a table with three fields: sales, customer, and qty .

 

p_TOPN = DATATABLE (
    "index", INTEGER,
    "TOPN", STRING,
    {
        { 1,"TOP1" },
        { 2,"TOP2" },
        { 3,"TOP3" }
    })

 

rank meature value

 

rank_by = SWITCH(TRUE(),
 ISFILTERED(Sheet1[sales]),RANKX(ALL(Sheet1[sales]),[qtySum]),
ISFILTERED(Sheet1[customer]),RANKX(ALL(Sheet1[customer]),[qtySum]),
 BLANK())

 

 

 I am now able to rank sales and customer sales separately. 

I hope to dynamically switch between res1 and res2 through field parameters p_muti

 

p_muti = {
    ("sales", NAMEOF('Sheet1'[sales]), 0),
    ("customer", NAMEOF('Sheet1'[customer]), 1)
}

 

 

 

 

TOP_dynam =
var n = SELECTEDVALUE('p_TOPN'[index])
var tab1 = ADDCOLUMNS( DISTINCT('Sheet1'[sales]),"rank",[rank_by],"qtySum",[qtySum])
var tab2 = ADDCOLUMNS( DISTINCT('Sheet1'[customer]),"rank",[rank_by],"qtySum",[qtySum])

var res1 = CALCULATE(FIRSTNONBLANK('Sheet1'[sales],'Sheet1'[sales]),FILTER(tab1,[rank]=n))
var res2 = CALCULATE(FIRSTNONBLANK('Sheet1'[customer],'Sheet1'[customer]),FILTER(tab2,[rank]=n))

return res1

 

 

 

 

 

Need Help