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 emails over the 10 weeks and not per week. Would you have a solution to suggest? Thank you for your feedback.

Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper 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.