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

Rank by multiple values

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Rank by multiple values

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

Rank-by-multiple-values2

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Rank by multiple values

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

Rank-by-multiple-values

 

Best Regards,

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

Re: Rank by multiple values

Hi @v-jiascu-msft  Dale,

 

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

 

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

 

 

Снимок экрана (57).png

 

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!

 

Community Support Team
Community Support Team

Re: Rank by multiple values

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

Rank-by-multiple-values2

 

 

Best Regards,

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

Re: Rank by multiple values

@v-jiascu-msft 

 

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