Dynamic Ranking within Category, Transactional data with Slicers
Hi All. I'm trying to build a matrix visualization that shows the top 5 selling items in each product category that will dynamically update based on filters applied by slicers. There are a few slicers applied to the report including Brand (3 different brands), Season (Fall, Summer, Winter, Spring), Year, and slot (discrete weekly time periods). I want the matrix to show the Categories in the far left column, then the Itemcodes that belong to that category in the next, follwed by the units sold of that item, with subtotals for the category level amongst the top 5 for each.
The source data is transactional data that shows every order (quantities are typically 1 per order), with Identifiers on each transaction for category.
I've successfully been able to rank all of the products without filtering by categories using the following measures:
Booked Units = calculate(sum(SalesOrdersFact[SOlOrderQty]),filter(SalesOrdersFact,SalesOrdersFact[SOlItemNumber]<>blank()))
Unit Rank = rankx(SUMMARIZE(ALLSELECTED(SalesOrdersFact),SalesOrdersFact[SOlItemNumber],"Booked Units",[Booked Units]),[Booked Units],,DESC)
I've tried multiple ways to rank amongst each item's category but it either shows nonsensical numbers, or all 1's. Some of the things I"ve tried include the following
That result is still giving me all 1's next to the item numbers in the matrix. My albeit rudimentary understanding of DAX would tell me that that measure would rank sales among categories i.e. the top selling category would have the rank of 1, and so on and so forth.
I'm looking to rank each product within each category. So For the SHIRTS category, what are the top 5 selling shirts in the data according to the slicers, and then the same for all the other categories. As such there will be 5 rows for each category (9 categories) for a total of 45 rows. Any additional thoughts?