Rank by multiple values

[D3K]

Hello everyone!

Any help will be appreciated in the next question

I have 3 tables:

1. Sales (date, customer ID, product ID, sales $)

2. Calendar (dates, months & so on)

3. Products (product ID, product name, brand, category and so on)

Sales is linked to Products by Product ID.

Sales is linked to Calendar by Date.

Sales is also linked to Customer List table by Client ID.

I need a measure to get 2 rankings of each product in 2 dimensions:

1. Rank of particular product inside it's parental brand in each customer's sales

2. Rank of particular product inside it's parental brand in the whole company's sales result.

The final target is to be able to compare, for example, product A1 inside brand A for customer X has the 4th place in it's (customer's) sales and at the same time this product is the 2nd in the sales of brand A in the company's totals.

Thanks a lot for your help!

[v-jiascu-msft]

Hi @D3K ,

One measure is enough. You can change the context to get what you want. Please download the demo from the attachment. 

Measure =
RANKX (
    ALL ( DimProduct[ProductKey] ),
    CALCULATE ( SUM ( FactSales[SalesQuantity] ) )
)

Best Regards,

[D3K]

Hi @v-jiascu-msft  Dale,

thank you for your answer. I've tried a hundreds of formula combinations, but the thing is the context 🙂

My problem was to get 1 common table of the next type:

Will use your solution with 2 tables, but maybe is it the way to create such table as I've described before?

Thanks a lot!

[v-jiascu-msft · Accepted solution]

Hi @D3K ,

That measure in the demo could be the customer level measure. Please try out this one as a company level rank measure.

CompanyLevelRank =
RANKX (
    ALL ( DimProduct[ProductKey] ),
    CALCULATE (
        SUM ( FactSales[SalesQuantity] ),
        ALL ( DimCustomer[CustomerLabel] )
    )
)

Best Regards,

[D3K]

@v-jiascu-msft 

Great, it works!! Thank you very much, Dale!