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 () ) )
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)
countries
| Japan |
| 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 ()
)
)