Forum Discussion

jwdal's avatar
jwdal
Frequent Visitor
1 year ago
Solved

Top N for multiple years

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 y...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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.