TOP N customers per week

[alefebvre]

Hello,

I would like to create a chart with the TOP 5 customers who have sent us the most emails per week. I can make a TOP 5 for the last 10 weeks but it takes into account the total number of emails over the 10 weeks and not per week. Would you have a solution to suggest? Thank you for your feedback.

[rajendraongole1]

Hi @alefebvre -Hope you already create a new calculated column to extract the week number from your date column as below:

calculated column:

WeekNumber = WEEKNUM('Emails'[Date], 2) -- 2 means the week starts on Monday

create  measure to calculate the total emails

EmailsPerWeek =
CALCULATE(
COUNT('Emails'[EmailID]),
ALLEXCEPT('Emails', 'Emails'[Customer], 'Emails'[WeekNumber])
)

Create another measure to rank customers based on the number of emails

CustomerRankPerWeek =
RANKX(
ALL('Emails'[Customer]),
[EmailsPerWeek],
,
DESC,
DENSE
)

create anothe measure to filter 

Top5CustomersEmailsPerWeek =
IF(
[CustomerRankPerWeek] <= 5,
[EmailsPerWeek],
BLANK()
)

Hope it works ,please check

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[alefebvre]

Hello,

Thank you for your solution, I will try to adapt my project with your solution. I will still contextualize my project just in case.

As you can see in the image to make my TOP N, I use the following 3 tables:

• Messages: Email table
• Date de reception: Calendar table
• Contacts: Table of sender contacts

As mentioned in my previous post, I manage to get the top 5 per week over the last 30 days but over the entire 30 days (see the image below), which does not correspond to my goal which is to make the top 5 per week over the last 30 days.

Again, thank you for your feedback.