Top N Customers / Products

[shona]

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

[shona]

@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 · Accepted solution]

@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

[shona]

Thanks @v-haibl-msft!

[v-haibl-msft]

@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]

@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]

@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