Forum Discussion
dynamic topN selection
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 () ) )
calculated 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/
Stachu Oh, I see, in that case do you know how to edit my header codes for a measure instead? When i create a measure using those codes i get an error "A single value for column 'countries' in table 'Sheet1' cannot be determined. ". I understand i need to use an aggregator function, but since i also need it to look at the values in each row to determine the rank, i'm unsure how i should continue with the code. Any help would be appreciated, thanks!
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 () ) )