how to return dynamic table according to selected value

[Anonymous]

Hi experts, I want to create a dynamic table that returns the selected top n table rather than a certern column.

here the sample data:

week		id		value
w1		E01		163
w1		E01		145
w1		E02		94
w1		E02		35
w1		E03		123
w1		E04		8
w1		E06		177
w1		E07		38
w2		E01		103
w2		E02		180
w2		E03		31
w2		E04		57
w2		E06		88
w2		E07		179
w2		E08		69
w3		E02		177
w3		E03		176
w3		E04		62
w3		E06		92

and the expecte result is rank of id based on amount of each week, and returns a table with week, id, sum of amount and top n of rank, notice that the rank is based on the slicer of the value, the slicer is another table with no relation with the data above:

topn

1

2

3

...

10,

for example, when topn slicer is 3, the expected table is: 

week		id		sum		rank
w1		E01		300		2
w1		E02		200		3
w1		E06		700		1
w2		E02		100		1
w2		E06		20		3
w2		E07		50		2
w3
…

when topn slicer is 4, then the table is:

week		id		sum		rank
w1		E01		300		2
w1		E02		200		3
w1		E06		700		1
w1		E07		166		4
w2		E02		100		1
w2		E06		20		3
w2		E07		50		2
w2		E01		10		4
w3
…

I create a table use the following dax, but fails to get selectedvalue and expected result, do you know how to solve this? Thank you.

VAR week=
FILTER(
SUMMARIZE('fact_table',
'fact_table'[week],
'fact_table'[id],
"amount",CALCULATE(SUM('fact_table'[base_currency_amount]))
)
VAR weekrk=
ADDCOLUMNS(
week
,
"rank",
RANKX(FILTER(week,
fact_table[week] = EARLIER(fact_table[week]) &&fact_table[id] = EARLIER(fact_table[id])
)
, [amount]
, , DESC,SKIP)
)
RETURN
FILTER(weekrk,[rank] <= SELECTEDVALUE(dim_topn[topn]))

[amitchandak · Accepted solution]

@Anonymous , Calculated tables can not use slicer value. 

You need to create a visual and use TOPN measures with other columns

examples

M1 =Sum('Table'[value])

TOPN using window = Sumx(KEEPFILTERS(Window(1,ABS,[Selected TOPN],ABS, ALLSELECTED('Table'[week],'Table'[ID]),ORDERBY([M1],Desc),,PARTITIONBY('Table'[week]))),[M1] )

TOPN using TOPN = Sumx(KEEPFILTERS(TOPN([Selected TOPN], filter(ALLSELECTED('Table'[week],'Table'[ID]), [week] = Max('Table'[week]) ),[M1], desc )), [M1])

Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ