dynamic topN selection

[Anonymous]

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 top 3 values and then shows the remaining values as "others":

countries	value
china	23452345
Japan	3456234
sinagpore	54322
others	18889

The code for the "countries" column is as follows:

Rank Top 3 Products and Others =

VAR rnk = RANKX(

Sheet1, 

[Name Total (MEASURE)],,, 

Dense

) <= 3

RETURN

IF(rnk , FORMAT(Sheet1[countries], ""), "Others") 

And the code for "value" column is as follows:

Rank Top Three Sales =

VAR rnk = RANKX(

CALCULATETABLE(

VALUES(Sheet1[countries]), 

ALLSELECTED()

),

Sheet1[value2],, 

DESC

) <= 3

RETURN IF(rnk, Sheet1[value]) 

All these code works fine, but now i want to create a dynamic topN function where instead of being 3 in the code, it's a variable that can be changed by selection. What i did was to create a new table called "Select topN" with values 2, 3 and 4, and then created a list slider of this table, and subsequently using a measure as follows:

Selected N = SELECTEDVALUE('Select topN'[select N])

However, when i change the "3" in my column codes above to the variable "selected N" the table stops working correctly, but instead just shows a single row with "Others". Any idea or other ways to get the variable selection? Thanks!

[Mariusz]

Hi @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.

[Arjunarao]

Hi sliu7

You can achive with these two Calculated columns

1. Rank =
RANKX ( 'Table', 'Table'[Value] )

2. ShowCountry =
IF ( 'Table'[Rank] <= 3, 'Table'[Country], "Other" )

Download the file from here:

https://drive.google.com/open?id=1Z8W1cBEww9FhbESpOAQBFBAKFifiSz_6

[Arjunarao]

My output

[Stachu]

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/

[Anonymous]

@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!

[Stachu · Accepted solution]

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