Top N for multiple years

Hi jwdal 

You can achieve this in Power BI by first identifying all customers that have been in the Top N for any year in your dataset, then using that list to filter your matrix so they show across all years even if they weren’t in the Top N for the most recent year.

Here’s one way to do it:

1. Create a measure to rank customers by year

DAXCopyEditRank By Year =
VAR SelectedYear = SELECTEDVALUE( Sales[Year] )
RETURN
    RANKX(
        FILTER(
            ALL(Sales[Customer], Sales[Year]),
            Sales[Year] = SelectedYear
        ),
        [Total Sales],
        ,
        DESC
    )

 

2. Create a table of all Top N customers across all years

DAXCopyEditTopN Customers =
VAR TopNValue = 10  
RETURN
    DISTINCT(
        FILTER(
            ADDCOLUMNS(
                ALL(Sales[Customer], Sales[Year]),
                "Rank", RANKX(
                    FILTER(
                        ALL(Sales[Customer], Sales[Year]),
                        Sales[Year] = EARLIER(Sales[Year])
                    ),
                    [Total Sales],
                    ,
                    DESC
                )
            ),
            [Rank] <= TopNValue
        )
    )

3. Use this table to filter your matrix

• Put Customer from your TopN Customers table into Rows.
• Put Year in Columns.
• Use your [Total Sales] measure in Values.

Because your TopN Customers table contains customers who made the Top N in any year, they will always appear for all years, with 0 showing where they had no sales.

Try creating a measure like

TopN Any Year =
VAR N = [Top N Value]
VAR YearsAndRanks =
    ADDCOLUMNS (
        ALLSELECTED ( 'Date'[Year] ),
        "@rank",
            CALCULATE (
                RANK ( ALL ( Customer[Customer Key] ), ORDERBY ( [Sales Amount], DESC ) )
            )
    )
VAR Result =
    IF ( COUNTROWS ( FILTER ( YearsAndRanks, [@rank] <= N ) ) >= 1, 1 )
RETURN
    Result

and add this to your matrix as a visual level filter, to show only when the value is 1.