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...
  • 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

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