Rank on higher aggregation level without loosing productdetails

[Marc1979]

Hi power bi friends,

I am looking for a way to create a DAX measure to calculate the rank of sales per day for each retailer belonging to a single distributor without loosing detailed information about the sales per product.

Table:

1. Distributor
2. Retailer
3. Product
4. Date
5. Sales

A single distributor can have multiple (up to hundreds) retailers.

On day level I want to rank the retailers per distributor based on total sales without loosing product details.

Desired result:

Distributor                Retailer           Product           Date            Sales   Ranking

Distributor A            Retailer A        Product A       01-01-2019     100      1

Distributor A            Retailer A        Product B        01-01-2019     50        1

Distributor A            Retailer A        Product C        01-01-2019     25        1

Distributor A            Retailer B        Product A       01-01-2019     50        2

Distributor A            Retailer B        Product B        01-01-2019     25        2

Distributor A            Retailer C        Product A       01-01-2019     10        3

Distributor A            Retailer C        Product B        01-01-2019     10        3

Is it possible to accomplish this by a measure? And if possible I only want to return the top 20 best selling retailers.

Thanks for your help!

Marc

[LivioLanzo]

Hi @Marc1979 

what is the relationship between products, retailers and distributors?

Can a product have many retailers / distributors? can a retailer have many distributors?

Or is it a hierarchical relationship like one between Year > Month > Day, Distributor > Retailer > Product?

[Marc1979]

Hi @LivioLanzo ,

Thanks for your reply, please find my answer below:

Products have multiple distributors

Retailers belong to one distributor

Kind regards,

Marc