cancel
Showing results for
Did you mean:
Highlighted
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

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

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

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

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.
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

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!

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

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.
Regular Visitor

Re: Rank by multiple values

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