Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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 () ) )
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.
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
My output
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)
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 () ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |