Forum Discussion
Ranking Sales By Multiple Categories
Hello,
I've been ping-ponging over YouTube about how to rank sales by customer and product.
I have a fact table for sales data (4M plus rows), a date dimension, a customer dimension, and a product dimension.
I want to rank sales by customer and within each customer a ranking of product sales. The matrix would have customer and product as row and total sales as the value. The output I'd like to see is this with a date slicer.
Customer Sales Rank Product Rank
Cust 1 $100 1
Product A $55 1
Product B $45 2
Cust 2 $75 2
Product C $45 1
Product B $30 2
I am getting values, but some of them are clearly wrong. I am wondering if I should create a separate calculated/derived table that aggregates the data and then employ rankx. One attempt has resulted in a correct ranking of the customers but when I add the product into the matrix, those rankings are completely off. It's as though a ranking of products is coming back.
Thanks.
I appreciate your help, but it didn't work. I only wanted one ranking column. This, however did work:
Rank By Customer =SWITCH(TRUE(),HASONEVALUE('Product Hierarchy'[Product Category]),RANKX(ALLSELECTED('Product Hierarchy)'[Product Category]), [Total Sales], , DESC),RANKX(ALLSELECTED('Customer'[Account]), [Total Sales], , DESC))
9 Replies
johnt75Super User
You could create a couple of measures like
Product Rank = VAR BaseTable = CALCULATETABLE ( SUMMARIZECOLUMNS ( Customer[Customer Name], Product[Product Name], "@sales", [Total Sales] ), ALLSELECTED ( Customer ), ALLSELECTED ( Product ) ) VAR Result = IF ( ISINSCOPE ( Product[Product Name] ) && NOT ISINSCOPE ( Customer[Customer Name] ), RANK ( BaseTable, ORDERBY ( [@sales], DESC ), PARTITIONBY ( Customer[Customer Name] ) ) ) RETURN Resultand
Customer Rank = IF ( ISINSCOPE ( Customer[Customer Name] ), RANK ( ALLSELECTED ( Customer[Customer Name] ), ORDERBY ( [Total Sales], DESC ) ) )
Irwanhello Anonymous
please check if this accomodate your need.
create two measure for Rank and Product Rank.
Rank =
var _Rank =
RANKX(
ALLSELECTED('Table'[Cust]),
[Sum of Sales],,
DESC,
Dense
)
Return
IF(
ISINSCOPE('Table'[Product]),
BLANK(),
_Rank
)Product Rank =
var _Rank =
RANKX(
ALLSELECTED('Table'[Product]),
[Sum of Sales],,
DESC,
Dense
)
Return
IF(
ISINSCOPE('Table'[Product]),
_Rank,
BLANK()
)Hope this will help.Thank youWhat are you thoughts about nesting the ranking? I'm thinking of something like this in a matrix. Once you click on the "+" in the matrix, the next level will then show the ranking of those items. So you have one column where the ranking is showing for customer and product.
Customer Sales Rank
- Cust 1 $1,000 1
Product A $600 1
Product B $300 2Product C $100 3
- Customer 2 $800 2
Product B $500 1
Product C $300 2
- Irwan
hello Anonymous
when not expanded, the matrix will be like this
no value in Product Rank because there is no product shown.
when expanded, then the matrix will be like this
when products are shown, then Product Rank has value. Rank Customer is also presented.
Product Rank will be calculated for each customer.
Detail is in attached pbix in my previous post.
Hope this will help.
Thank you.
- Ashish_Mathur
Hi,
Do you want to create 2 columns in the matrix - Rank and Product rank or just one rank column in the matrix would do?
I tried this without success:
Sales Ranking =IF(ISINSCOPE('US SALES ABT COMBINED DETAILS (sap)'[Sold-to Name]),RANKX(ALLSELECTED('US SALES ABT COMBINED DETAILS (sap)'[Sold-to Name]),[Total Sales], , DESC, Dense),IF(ISINSCOPE('US SALES ABT COMBINED DETAILS (sap)'[USD Product Category]),RANKX(ALLSELECTED('US SALES ABT COMBINED DETAILS (sap)'[USD Product Category]),[Total Sales], , DESC, Dense)))
srlabheHi Try to follow the below stepsStep 1: Create a Total Sales MeasureA measure for total sales should be created. This measure will sum the sales amount from the fact table.CodeTotal Sales = SUM('Fact Sales'[SalesAmount])Step 2: Create a Customer Rank MeasureA measure for ranking customers based on their total sales should be created. The RANKX function will be used, iterating over all customers.CodeCustomer Rank = RANKX(ALL('Customer'[CustomerName]), [Total Sales], , DESC, DENSE)Step 3: Create a Product Rank within Customer MeasureA measure for ranking products within each customer based on their total sales should be created. The RANKX function will be used, iterating over all products for the current customer context.CodeProduct Rank within Customer = RANKX(ALLSELECTED('Product'[ProductName]), [Total Sales], , DESC, DENSE)Step 4: Construct the Matrix VisualA matrix visual should be added to the report. The 'CustomerName' from the Customer dimension should be placed in the "Rows" field, followed by 'ProductName' from the Product dimension in the "Rows" field. The 'Total Sales' measure should be placed in the "Values" field. The 'Customer Rank' and 'Product Rank within Customer' measures can also be added to the "Values" field for display, or used for sorting purposes.To rank sales by customer and then by product within each customer in Power BI, three measures are required: Total Sales, Customer Rank, and Product Rank within Customer. These measures are then used in a matrix visual with 'CustomerName' and 'ProductName' in the rows and 'Total Sales' in the values. The ranking measures can be used for display or sorting.I appreciate your help, but it didn't work. I only wanted one ranking column. This, however did work:
Rank By Customer =SWITCH(TRUE(),HASONEVALUE('Product Hierarchy'[Product Category]),RANKX(ALLSELECTED('Product Hierarchy)'[Product Category]), [Total Sales], , DESC),RANKX(ALLSELECTED('Customer'[Account]), [Total Sales], , DESC))
