Forum Discussion
Anonymous
7 years agoNot applicable
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...
- 7 years ago
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 () ) )
Arjunarao
7 years agoResolver I
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
- Arjunarao7 years agoResolver I
My output