Forum Discussion

Sean's avatar
Sean
Community Champion
9 years ago
Solved

TOPN Function - Question/Comment

Okay we all know TOPN doesn't like ties and returns an error My question is why then if you use TOPN as a filter in a CALCULATE does it include all tied values? Very misleading - Consider this simp...
General Comment
  • Sean's avatar
    Sean
    9 years ago

    Anonymous

    Thanks for the suggestion (AVERAGEX) - the issue there is that my real table has multiple rows of data per asset per transaction

    Hope this makes sense! :smileyhappy: So it doesn't give the correct result!

     

    Anyway today I ran into another thing that could trip up the TOPN (seems very obvious now)

    So the other thing that can trigger this message "Visual can't be displayed..." with the TOPN

    When the Calendar rolls over into 2017 (a new year) however there's no data for 2017 (the new year) yet

     

    You may think IF ( ISBLANK (  [TOPN Measure] ), "N/A", [TOPN Measure] ) would fix this but nope!

    This patterns however seems to work with the RANKX measure

     

    So for the TOPN Measure you'll have to use

    Top Asset = IFERROR ( TOPN ( 1, ALLSELECTED ( Table[Asset] ), Table[Asset], ASC ), "N/A" )

    Otherwise when your calendar rolls over into the new year but there's no data yet the visual won't display

    BTW => this happens in a Table Visual where the first field is YEAR from a Calendar table (and the whole table won't display)

    In a Card Visual the same would happen but only if you select 2017 from the YEAR slicer - then it would not display

     

    Hope this makes sense! :smileyhappy: