I currently have a set of data as follow: countriesvalueJapan3456234china23452345france6543usa12346sinagpore54322 And a top 3 table, which shows only the top 3 values and then shows the remaining values as "others": countriesvaluechina23452345Japan3456234sinagpore54322others18889 The code for the "countries" column is as follows: Rank Top 3 Products and Others = VAR rnk = RANKX(Sheet1, [Name Total (MEASURE)],,, Dense) <= 3RETURN 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) <= 3RETURN 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!

Forum Discussion

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

Need Help

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

6 Replies

Stachu
Community Champion
7 years ago
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
  Not applicable
  7 years ago
  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
    Community Champion
    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 () ) )
Arjunarao
Resolver I
7 years ago
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
  Resolver I
  7 years ago
  My output
Mariusz
Community Champion
7 years ago
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.

Forum Discussion

dynamic topN selection

6 Replies

Related content

Dynamic images based on selections

TopN

TopN

Dynamic TOPN

Dynamic Filtering based on date selection