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 () ) )
Stachu
Community Champion
7 years agocalculated column is static and doesn't change with the slicer selection
you can achieve the behaviour you want with a measure
here is an article explaining the difference between the two:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/