Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sfleming
Regular Visitor

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

 

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!

1 ACCEPTED SOLUTION

@sfleming

Try to replace Products[Product Category] for Products[Product]. Or share your .pbix file

View solution in original post

6 REPLIES 6
popov
Resolver III
Resolver III

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.

 

example.PNG

@popov - Amazing such a simple solution was the fix. Thank you for your help.

@sflemingYou're welcome

@sfleming

Try to replace Products[Product Category] for Products[Product]. Or share your .pbix file

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.