Top N for multiple years

[jwdal]

I'd like to create a matrix that shows years as columns and customer names as rows.  The cells will contain total sales for each of the customers by year.  The first column would be the most recent year and would have the top N customers, sorted by the sales for that year in descending order.  The other columns would also have the customers listed in the first column with sales in each of the years.

The complication is that I'd like to show customers that were in the top N for any year that's in my visual.  So there will be more than N rows.  If ACME wasn't in the top N for the most recent year but was in the top N for the prior year, the first column would still show the sales for ACME.  If there were no sales to ACME, the first column would show zero.

[rohit1991 · Accepted solution]

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.

[jwdal]

I tried johnt75 and rohit1991 solutions and am getting this error message for both:  "Column 'Sales' cannot be found or may not be used in this expression."

[johnt75]

You need to create a measure like

Sales Amount = SUM( 'Table'[Sales] )

[johnt75 · Accepted solution]

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.

[rohit1991 · Accepted solution]

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.