RANKX Change Measure not working when TopN filter applied

[kristinamol]

Hello,

I'm working on a Power BI page where I need to create:

• A table that shows dynamic top 50 ranked Brands, Product, Other topics (ranking is based on topics performance) that would change dynamically based on Topic Type filter selected, and
• 4 KPI tables that are based off of the main table and show:

1) top 3 ranked topics (columns: Rank, Rank Change vs previous month, Topic)

2) top 3 with biggest change in rank vs previous month (same columns as above)

3) top 3 with biggest decline in rank vs previous month (same columns as above)

and

4) top 3 NEW topics (if they weren’t in the top 50 last month or weren’t in the ranking at all before but made it to top 50 this month)  (same columns as above)

The data I’m working with comes from 1 table. Please see data table here.

My page-level filters are: Country (single select) and Topic Type (multi-select: Brand/Product/Other).

The measure I’m using for Rank as of latest month is this:

NB: Rank Metric is what ranking is based on, e.g. sales or volume

Rank = var latest_rank =  RANKX (

    ALLSELECTED (

        'Table'[Topic],'Table’[Topic Type],’Table'[Market]),

    CALCULATE (

        SUM ( 'Search Next100'[Rank metric] ),

        ALLEXCEPT('Table’, 'Table'[Topic],'Table’[Topic Type],’Table'[Market])

        )

    ,

    ,

    DESC, DENSE

)

RETURN latest_rank

The measure I’m using for Rank Change vs prev.month is this:

Rank Change =

var latest_rank = [Rank]

var previous_rank = RANKX (

    ALLSELECTED (

        'Table'[Topic],'Table’[Topic Type],’Table'[Market]),

    CALCULATE (

        SUM ( 'Search Next100'[Rank Metric Previous] ),

        ALLEXCEPT('Table’, 'Table'[Topic],'Table’[Topic Type],’Table'[Market])

        )

    ,

    ,

    DESC, DENSE

)

return

if(latest_rank<51 && previous_rank>50, "NEW",

if(previous_rank=BLANK(), "NEW",

 previous_rank-latest_rank))

The issue I’m having is when I’m trying to filter the tables to only show top 50 (rather than all topics) in the main table and top 3 based off of top 50 in the KPIs table. Initially I did apply a visual filter on the tables to show Top N (i.e. 50) but that doesn’t work with Topics that are NEW, it changes their value in Rank change column from NEW to something incorrect (as these topics are not in the top 50 for the previous month). Because this is a dynamic table, it doesn’t just show top 50 rows but it recalculates the Rank Change measure based on TopN filter.

E.g. this is what the Main table looks like without top50 filter applied, showing all topics (Rank and Change look correct): 

However, as soon as I try to apply a visual filter, whether by setting Rank to be <51 or use Topics to show top N based on Rank measure, some Change column values change: 

As such I was wondering what would be the right way to be able to show filter my tables to show only top 50 topics (i.e. rows) without recalculating the Rank Change measure? And then when that is done, how would I filter on top 3 topics based on my requirements?

Thanks

[some_bih]

Hi @kristinamol yes, your apprach could work as you compare two period/ measures per topic(filters are simple visual filter), you will need kind of Calc.table to calculate different output per topic and copmare it on row/ topic level.

[some_bih]

Hi @kristinamol Your issue is that in first measure, Rank, you used ALLSELECTED and called it in second measure. This is not best practice as results in second measure are hard, almost imposible to interpret/ resolve issue, as second measure calling first measure with ALLSELECTED. The best practice using ALLSELECTED is "just single visual" .

Rework your second measure / find other solution.

Did I answer correctly? Kudos appreciate / accept sokution.

[kristinamol]

Hi @some_bih , could you advise how to rework the Rank Change measure please? I assume it would be along the lines of creating a separate calculated table that holds top 50 topics for the latets month. And then reworking the Rank Change measure to calculate rank change for topics within the calculated table. However, I'm not exactly sure if this is the right approach, so would appreciate guidance on what to do