MATRIX OF TOP CUSTOMERS AND TOP PRODUCTS

[Anonymous]

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

[v-chuncz-msft]

@Anonymous,

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

[Anonymous]

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.