Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Category Rank = RANKX(SUMMARIZE(ALLSELECTED(SalesOrdersFact),SalesOrdersFact[SOlItemNumber],Products[Product Category],"Booked Units",[Booked Units],allexcept(SalesOrdersFact,Products[Product Category])))
Any help would be much appreciated.
Thanks!
Solved! Go to Solution.
Try to replace Products[Product Category] for Products[Product]. Or share your .pbix file
Hello, @sfleming
If you have active relationship between Products and SalesOrdersFact, you would try this formula
Category Rank =
RANKX ( ALLSELECTED ( Products[Product Category] ), [Booked Units] )
Thanks @popov,
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?
To Clarify. This is what I would like the result to look like basically, with additional measures like $ sales and margin as columns to the right for the same products during the same time period.
Try to replace Products[Product Category] for Products[Product]. Or share your .pbix file
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |