Forum Discussion

D3K's avatar
D3K
Advocate II
7 years ago
Solved

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!

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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,

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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,

    • D3K's avatar
      D3K
      Advocate II
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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,