Bottom 10 considering repeated value.

[uj91]

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]

 )

[danextian]

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]

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

[cengizhanarslan]

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]

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

[cengizhanarslan]

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.

[Amar_Kumar]

@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

[Amar_Kumar]

@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 · Accepted solution]

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]

Can you mark it as a accepted solution, please. 

[uj91]

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 

[FBergamaschi]

It is unclear to me

1 - what you mean by field parameter

2 - what KPI Measures_new is (a measure I assume, pls confirm)

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

[FBergamaschi]

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