Forum Discussion

alefebvre's avatar
alefebvre
New Member
2 years ago

TOP N customers per week

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 email...
Need Help
rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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's avatar
alefebvre
New Member
2 years ago

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.