DAX to replicate TopN filter to make it Dynamic, show Top all time - but by week

[mark_endicott]

Hi, 

I am trying to replicate the function of a TopN visual level filter, so I can use it with a What If parameter to make it dynamic for the users of a report. 

I'd like to show the TopN of an item all time, but then break this down by the value that item acheived each week. I have tried several options, but I cannot seem to get it to ignore the date filter to find "all time" then re-apply the date filter to show the top N across time. 

Below is an example, where 'MARKET SALES' is my fact table, 'TABLE 1'[TABLE Code] is the item I want to do the TopN for and I'm trying to filter the Calculate table by the whole calendar.

SUMX (
    TOPN (
        [Top Number Selector Value],
        CALCULATETABLE (
            SUMMARIZE ( 'MARKET SALES', 'TABLE 1'[TABLE Code] ),
            ALL ( 'RLS CALENDAR' )
        ),
        [Total value], DESC
    ),
    [Total value]
)

Unfortunately this presents more items from 'TABLE 1'[TABLE Code] than the number selected in the TOP Number slicer.

[mark_endicott · Accepted solution]

I have resoloved this by using a RANKX, inside a calculate that removes the calendar context from RANKX, here's my solution:

VAR Number_to_Rank = [Top Number Selector Value]
VAR Val = [Total value]
RETURN
    IF (
        CALCULATE (
            RANKX ( ALLSELECTED ( 'TABLE 1'[TABLE Code] ), [Total value] ) <= Number_to_Rank,
            ALL ( 'RLS CALENDAR' )
        ),
        Val
    )