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 im...
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
        )
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