cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sfleming Frequent Visitor
Frequent 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

Accepted Solutions
popov Member
Member

Re: Dynamic Ranking within Category, Transactional data with Slicers

@sfleming

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

View solution in original post

6 REPLIES 6
popov Member
Member

Re: Dynamic Ranking within Category, Transactional data with Slicers

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] )

sfleming Frequent Visitor
Frequent Visitor

Re: Dynamic Ranking within Category, Transactional data with Slicers

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?

popov Member
Member

Re: Dynamic Ranking within Category, Transactional data with Slicers

@sfleming

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

View solution in original post

sfleming Frequent Visitor
Frequent Visitor

Re: Dynamic Ranking within Category, Transactional data with Slicers

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

sfleming Frequent Visitor
Frequent Visitor

Re: Dynamic Ranking within Category, Transactional data with Slicers

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

popov Member
Member

Re: Dynamic Ranking within Category, Transactional data with Slicers

@sflemingYou're welcome

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)