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

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