Forum Discussion
Anonymous
7 years agoNot applicable
dynamic topN selection
I currently have a set of data as follow: countries value Japan 3456234 china 23452345 france 6543 usa 12346 sinagpore 54322 And a top 3 table, which shows only the...
- 7 years ago
you will need a table that actually has "Others" in it, something like this (countries in my DAX code)
countriesJapan china france usa sinagpore Others that table needs to have a single direction 1:many join with Sheet1 table
then you create measure Rank
Rank = RANKX(ALLSELECTED(countries[countries]),CALCULATE(SUM(Sheet1[value])))
the TopN slicer measure (can be done with WhatIf parameter)
TopN Value = SELECTEDVALUE('TopN'[TopN], BLANK())and then the measure with value
Measure = VAR __TopN = TOPN ( [TopN Value], ALL ( Sheet1 ), CALCULATE ( SUM ( Sheet1[value] ) ), DESC ) RETURN IF ( [Rank] <= [TopN Value], SUM ( Sheet1[value] ), IF ( SELECTEDVALUE ( countries[countries] ) = "Others", CALCULATE ( SUM ( Sheet1[value] ), ALL ( countries[countries] ) ) - SUMX ( __TopN, [value] ), BLANK () ) )
Mariusz
Community Champion
7 years agoHi Anonymous
To use Selected N = SELECTEDVALUE('Select topN'[select N]) in your expression it has to be a measure not a column.
Column is executed at load time of your model and can not be changed.
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.