cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
khoahong Regular Visitor
Regular Visitor

MATRIX OF TOP CUSTOMERS AND TOP PRODUCTS

Hello everyone,

 

I am stucking with a requirement. Please help me. Thank you very much.

 

There is a sales table with some basic fields: Date, CustomerID, ProductID, Sales_Amount.

 

Now I need a table that show the YTD sales of top 10 customers and top 10 products (top 10 products of the whole company, not for these top 10 customers). The data also should be sorted from largest YTD sales to smallest YTD sales by both dimensions (customers and products).

 

And users can filter by year and month too. If they choose year only, the number is the amount of whole year; if they continue choosing a month, the sales is YTD sales.

 

The table example is as below:

 

                             | No.1 product | No.2 product | No.3 product | ...| No.10 product | Total

No.1 customer

No.2 customer

No.3 customer

.

.

.

No.10 customer 

Total

2 REPLIES 2
Community Support Team
Community Support Team

Re: MATRIX OF TOP CUSTOMERS AND TOP PRODUCTS

@khoahong,

 

Try using RANKX Function to add a measure and take advantage of Visual level filters.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
khoahong Regular Visitor
Regular Visitor

Re: MATRIX OF TOP CUSTOMERS AND TOP PRODUCTS

hi @v-chuncz-msft,

 

I tried that also. I created the measures for customerID and productID as well. But it didnt work. I dont know what I am doing wrong.

 

First, I created the measure for YTD sales:

 

Totalsales = CALCULATE(SUM([Sales])

YTDsales = CALCULATE([Sales],DATESYTD([Date])

 

Then I created the RANKX measure:

 

rankcustomer = RANKX(ALL([customerID]),[YTDsales],,Desc)

rankproducts = RANKX(ALL([productID]),[YTDsales],,Desc)

 

Then I applied these measures to the matrix with filter that rankcustomer <= 10 & rankproducts <= 10. At first, it showed the top 10 customers and top 10 products as I want. But then I added [YTDsales] as the value of matrix, it didnt work anymore.