Forum Discussion

uj91's avatar
uj91
Helper I
8 months ago
Solved

Bottom 10 considering repeated value.

So i need bottom 10 keywords  in a table considering repeated value meaning just last 10 entries of keyword. ALso i dot want to show value 0. 


For example in the above table there are impressions having value 1 for 20 rows , i only need to pick or show the last 10 entries with least impressions (Not showing 0). 

Also impression is coming from a field parameter with below formula 

KPI Measures_new = SWITCH(
    SELECTEDVALUE('Search Insights'[Search Insights Order]),
   0 ,[Total Impressions_Search],
1 ,[Total Clicks_Search],
2, [Total Media Cost_Search],
3 , [CTR_Search],
 4 , [CPC_Search],
 5  ,[CPM_Search]
 )
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • uj91's avatar
    uj91
    8 months ago
    I tweaked your fomrula and it worked !

    Bottom     10 Keywords =
    VAR BaseTable =
        FILTER (
            ALL ( 'Reporting.Search'[KeywordText] ),
            [KPI Measures_new] <> 0
                && NOT ISBLANK ( [KPI Measures_new] )
        )

    VAR Bottom10Table =
        TOPN (
            10,
            BaseTable,
            [KPI Measures_new], ASC,
            'Reporting.Search'[KeywordText], ASC   -- deterministic tie-breaker
        )

    RETURN
        IF (
            CONTAINS (
                Bottom10Table,
                'Reporting.Search'[KeywordText],
                SELECTEDVALUE ( 'Reporting.Search'[KeywordText] )
            ),
            1
        )

12 Replies

  • danextian's avatar
    danextian
    Super User
    8 months ago

    hI uj91 

     

    Try the following:

    Bottom Keywords = 
VAR _N = 10 -- Create virtual table with all keywords, ignoring outer row context
VAR AggregatedTable =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( 'KeywordsTable' ),
            -- removes row context effects
            'KeywordsTable'[Keyword]
        ),
        "TotalValue", CALCULATE ( SUM ( 'KeywordsTable'[Value] ) ) --  replace wit the actual measure
    ) -- Exclude keywordswith total value 0
VAR FilteredTable =
    FILTER ( AggregatedTable, [TotalValue] <> 0 ) -- Return only the bottom N keywords
VAR _keywords =
    SELECTCOLUMNS ( TOPN ( _N, FilteredTable, [TotalValue], ASC ), [Keyword] )
RETURN
    CALCULATE (
        SUM ( KeywordsTable[Value] ),
        KEEPFILTERS ( KeywordsTable[Keyword] IN _keywords )
    )

     

    • uj91's avatar
      uj91
      Helper I
      8 months ago

      The formula is showing more entries and need soemthing which can filter the visual as the measure field here is a field parameter 

  • Amar_Kumar's avatar
    Amar_Kumar
    Super User
    8 months ago

    uj91 

    Bottom 10 Keywords :=
    VAR RankedValue =
    RANKX (
    FILTER (
    ALL ( 'Table'[Keyword] ),
    [KPI Measures_new] <> 0
    && NOT ISBLANK ( [KPI Measures_new] )
    ),
    [KPI Measures_new],
    'Table'[Keyword], -- tie-breaker → ensures “last 10”
    ASC,
    SKIP
    )
    RETURN
    IF ( RankedValue <= 10, 1 )

    How to apply (must follow exactly)

    1. Table visual:

      • Keyword

      • KPI Measures_new

    2. Visual-level filter:

      • Bottom 10 Keywords → is 1

    3. (Optional) Sort:

      • KPI Measures_new → Ascending

    • uj91's avatar
      uj91
      Helper I
      8 months ago
      I tweaked your fomrula and it worked !

      Bottom       10 Keywords =
      VAR BaseTable =
          FILTER (
              ALL ( 'Reporting.Search'[KeywordText] ),
              [KPI Measures_new] <> 0
                  && NOT ISBLANK ( [KPI Measures_new] )
          )

      VAR Bottom10Table =
          TOPN (
              10,
              BaseTable,
              [KPI Measures_new], ASC,
              'Reporting.Search'[KeywordText], ASC   -- deterministic tie-breaker
          )

      RETURN
          IF (
              CONTAINS (
                  Bottom10Table,
                  'Reporting.Search'[KeywordText],
                  SELECTEDVALUE ( 'Reporting.Search'[KeywordText] )
              ),
              1
          )
    • Amar_Kumar's avatar
      Amar_Kumar
      Super User
      8 months ago

      uj91 

      Bottom 10 Keywords =
      VAR BaseTable =
          FILTER (
              ALL ( 'Reporting.Search'[KeywordText] ),
              [KPI Measures_new] <> 0
                  && NOT ISBLANK ( [KPI Measures_new] )
          )

       

      VAR Bottom10Table =
          TOPN (
              10,
              BaseTable,
              [KPI Measures_new], ASC,
              'Reporting.Search'[KeywordText], ASC   -- deterministic tie-breaker
          )

       

      RETURN
          IF (
              CONTAINS (
                  Bottom10Table,
                  'Reporting.Search'[KeywordText],
                  SELECTEDVALUE ( 'Reporting.Search'[KeywordText] )
              ),
              1
          )
    • uj91's avatar
      uj91
      Helper I
      8 months ago

      This is as close as i could have goten to the solution , thanks ! However its showing more than 10 entries , it is showing 22 entries 

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    8 months ago
    Rank_Bottom10 =
    VAR Rank_ =
    RANKX ( FILTER ( ALLSELECTED ( DimKeyword[Keyword] ), [KPI Measures_new] > 0 ), [KPI Measures_new], , ASC, DENSE)
    RETURN
    IF ( Rank_ <= 10, Rank_ )
     
    Use the formula above, and then use it in your visual as a measure filter by filtering only non-blank values.
    • uj91's avatar
      uj91
      Helper I
      8 months ago

      not working as expected. I need to show only 10 bottom entries with least value (values repeated are fine). 

      • cengizhanarslan's avatar
        cengizhanarslan
        Super User
        8 months ago

        Blank values brokes the formula. Use below, I tested it, works fine.

         

        Rank_Bottom10 =
        VAR Rank_ =
        RANKX ( FILTER ( ALLSELECTED ( DimKeyword[Keyword] ), [KPI Measures_new] > 0 ), [KPI Measures_new], , ASC, DENSE)
        RETURN

        IF ( Rank_ <= 10 && [KPI Measures_new] > 0, Rank_ )
         
        Use the formula above, and then use it in your visual as a measure filter by filtering only non-blank values.
    • FBergamaschi's avatar
      FBergamaschi
      Super User
      8 months ago

      Hi, this works, hope it is what you were asking

       

      It is a measure, it is using Product[Color], a column you will have to substitute with the one on which you are doing the RANK column

       

      Test =
      VAR NumberOfValuesToShowFromBottom = 4
      VAR MaxRank =
      MAXX ( ALL ( 'Product'[Color] ), [RANK] )
      VAR MinRank = MaxRank - NumberOfValuesToShowFromBottom
      VAR CurrentRank = [RANK]
      RETURN
      IF (
          CurrentRank <=MaxRank && CurrentRank >= MinRank,
          CurrentRank
      )
       

      If this helped, please consider giving kudos and mark as a solution

      me in replies or I'll lose your thread

      Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

      Consider voting this Power BI idea

      Francesco Bergamaschi

      MBA, M.Eng, M.Econ, Professor of BI