Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
  • Stachu's avatar
    Stachu
    7 years ago

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