Forum Discussion

shona's avatar
shona
Frequent Visitor
9 years ago
Solved

Top N Customers / Products

Hello,

 

I am hoping someone can help me. I am trying to calculate my top 10 customers for the current month to date and the previous month to date to show in a visual alongside each other with the sales value.

 

I have managed to do this in a new table but if a customer is a top customer in one month but not the other then there are blank values.


For example, customer A is a top 10 customer in the current month to date but not in the previous month to date so the sales figure for the previous month to date is showing as blank.

 

I want to show the value for both months.

 

Any suggestions?

 

Shona

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    shona

     

    Do you mean that you want to show the Top N customers of current month and previous month in the same column chart as below?

     

    If yes, we need to create two tables for current month and previous month, then union these two tables, and use the columns of the union table in a column chart.

     

    CurrentMRank = 
    SUMMARIZECOLUMNS (
        Table1[Customer],
        "CurrentMTD", [CurrentMTD],
        "PreMTD", [PreMTD],
        "Rank", [Rank_CurrentMTD],
        "Type", "CurrentMonth"
)

     

     

    PreMRank = 
    SUMMARIZECOLUMNS (
        Table1[Customer],
        "CurrentMTD", [CurrentMTD],
        "PreMTD", [PreMTD],
        "PreMRank", [Rank_PreMTD],
        "Type", "PreMonth"
)

     

    Current&PreM = 
UNION ( CurrentMRank, PreMRank )

     

    Best Regards,

    Herbert

6 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    shona

     

    I’m not sure what is the final table visual you desired. But you can try with following measures to see if it is your expected result.

    I tested with a fact and a calendar table, they have both direction relationship with Date key.

     

    Create a measure to calculate the month to date sales.

    CurrentMTD = 
TOTALMTD ( SUM ( Table1[Sales] ), 'Calendar'[Date] )

    Create a measure to rank the CurrentMTD.

    Rank = 
RANKX ( ALL ( Table1[Customer] ), [CurrentMTD],, DESC )

    Create a measure to calculate the pre-month to date sales.

    PreMTD = 
    CALCULATE (
        SUM ( Table1[Sales] ),
        DATEADD ( DATESMTD ( Table1[Date] ), -1, MONTH )
)

    Use the visual level filter to show the TopN customers.

     

    Best Regards,

    Herbert

    • shona's avatar
      shona
      Frequent Visitor
      9 years ago

      v-haibl-msft

       

      Thanks for your response but I am not sure if this will work as I want to show the top customers in both the current month to date and previous month to date so if I filter the visual by top customers for each then I think it will only show those who were a top customer in both months?

       

      Shona

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        shona

         

        My above solution will show the top customers for current month. If you want to show both months’ top n, we need to create another measure to ranks the PreMTD.

         

        Rank_CurrentMTD = 
RANKX ( ALL ( Table1[Customer] ), [CurrentMTD],, DESC )

         

        Best Regards,

        Herbert

  • shona's avatar
    shona
    Frequent Visitor
    9 years ago

    v-haibl-msft I wanted to show both values on a column chart in the same visual but I guess this is not possible

    • v-haibl-msft's avatar
      v-haibl-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      shona

       

      Do you mean that you want to show the Top N customers of current month and previous month in the same column chart as below?

       

      If yes, we need to create two tables for current month and previous month, then union these two tables, and use the columns of the union table in a column chart.

       

      CurrentMRank = 
    SUMMARIZECOLUMNS (
        Table1[Customer],
        "CurrentMTD", [CurrentMTD],
        "PreMTD", [PreMTD],
        "Rank", [Rank_CurrentMTD],
        "Type", "CurrentMonth"
)

       

       

      PreMRank = 
    SUMMARIZECOLUMNS (
        Table1[Customer],
        "CurrentMTD", [CurrentMTD],
        "PreMTD", [PreMTD],
        "PreMRank", [Rank_PreMTD],
        "Type", "PreMonth"
)

       

      Current&PreM = 
UNION ( CurrentMRank, PreMRank )

       

      Best Regards,

      Herbert