Show top category per year on time chart

[emdnz]

Hey all, I feel like I'm very close to the answer but just can't quite conceptualise. 

I have a survey I'm processing that has yearly survey submissions across 4 categories of questions. I'm trying to create a bar chart with "year" as x-axis, and the top category based on importance score per year. I've pasted a screenshot below where I've manually highlighted the bars that I want showing in my timeline. So in the final product I'd have a timeline where it shows the category with the max score for that year, then presumably use the 'legend' function to have that impact the colors as well.

I'm guessing I could achieve this with a calculated table where each year is 1 row and it shows the max category with the max score? But I just don't quite know how to do this. I've tried some different Top N filters as well but it just seems to show the top N category across the entire dataset rather than show it per year. 

Many thanks in advance!

Year	Company	Category	Reality	Importance
2020	Company 1	Across Border Experience	3.41	1.83
2020	Company 1	Balanced Projects	3.42	2.93
2020	Company 1	Company and Department Familiarity	3.62	2.74
2020	Company 1	Duration	3.44	2.6
2020	Company 2	Across Border Experience	2.25	2
2020	Company 2	Balanced Projects	3.33	2.94
2020	Company 2	Company and Department Familiarity	3.43	2.99
2020	Company 2	Duration	3.43	2.99

[parry2k · Accepted solution]

@emdnz you can easily achieve this by using the new WINDOW DAX function, here is the measure that you can use, change the column and table name as per your model.

Also, you can check the full playlist on windows functions here, and more specific to your question, check this video

Subscribe youtube.com/@PowerBIHowTo for Power BI videos.

//add base measure
Importance Measure = MAX ( 'Table'[Importance] ) 

//add measure to use in the visual to show top category by year
Top Category by Year = 
CALCULATE ( 
    [Importance Measure],
    KEEPFILTERS ( 
        WINDOW (
            1, ABS,
            1, ABS,
            ADDCOLUMNS (
                SUMMARIZE ( 
                    ALL ( 'Table' ),
                    'Table'[Year],
                    'Table'[Category]
                    
                ),
                "@Importance", [Importance Measure]
            ),
            ORDERBY ( [@Importance], DESC ),
            PARTITIONBY ( 'Table'[Year] )
        )
    )
)

[emdnz]

You are a magician sir, I understand nothing of your measure but it worked! Thank you so much.

[parry2k]

@emdnz glad to hear it worked. It is actually very simple measure, I would highly recommend to check the playlist of window dax functions. It will clarify lot of concepts. Good luck. 

[amitchandak]

@emdnz , Use a TOPN Filter on Category

or create a TOPN Measure based on Category and use that in Visual

Both will have a little bit different behavior

Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE

[emdnz]

Thanks @amitchandak , living up to your super user name. I've tried the top n filter for category but it seems to then show the 1 top category across all years, it doesn't appear to take the top category PER year. Based on the screenshot I shared earlier, I'm expecting 2020 and 2021 to have different categories from all other years based on their avg score (ignore the "max" in the screenshot, that was just me testing)