Top5 customers per group per week

[Bomba]

Hi there,

I'm working on a project and need to get table with the top 5 customers per group per week. 

for example, I have 3 groups at the company which sell the same items to various customers each week. I need to get a top 5 of customers for each of the groups per week as well as getting a top 5 customers of all the groups combined.

can anyone help me with this equation?

thanks!

[shafiz_p · Accepted solution]

Hi @Bomba ,
As I can understand, that you want to find out top 5 customer of each group each week. You need to create 2 measure to achieve this. Rank measure to evaluate rank and then filter only top 5.

I have Product Table, Date Table and Sales Table and relationship is one to many. I want to find out top 5 products by revenue of each product line each week. Similar situation. 

To evaluate rank, you can try below code:

RankProduct = 
RANKX(
    ALLSELECTED(DimProduct[EnglishProductName]),
    [Revenue],
    ,
    DESC,
    DENSE
)

Revenue = Sum of sales.

To find top 5, try below code:

Top 5 = 
IF(
    [RankProduct] <= 5,
    1,
    0
)

Here is the desired output:

To achieve this final result, just select the matrix visual, go to filter pane and place the top 5 measure into filter this visual section and set value is 1 and apply. See image:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Omid_Motamedise · Accepted solution]

If you want to use power query, use the below code

let

    Source = YourDataSource,

    Grouped = Table.Group(Source, {"Group", "Week", "Customer"}, {{"Total Sales", each List.Sum([Sales]), type number}}),

    Ranked = Table.AddColumn(Grouped, "Rank", each Table.AddIndexColumn(Table.Sort(Table.SelectRows(Grouped, each [Group] = _[Group] and [Week] = _[Week]), {"Total Sales", Order.Descending}), "Index", 1, 1)[Index]),

    FilteredTop5 = Table.SelectRows(Ranked, each [Rank] <= 5),

    AllCustomers = Table.Group(Source, {"Customer"}, {{"Total Sales", each List.Sum([Sales]), type number}}),

    AllRanked = Table.AddColumn(AllCustomers, "Rank", each Table.AddIndexColumn(Table.Sort(AllCustomers, {"Total Sales", Order.Descending}), "Index", 1, 1)[Index]),

    Top5All = Table.SelectRows(AllRanked, each [Rank] <= 5)

in

    {FilteredTop5, Top5All}

[Anonymous · Accepted solution]

Hi,

Thanks for the solutions Omid_Motamedise  and shafiz_p  offered,and i want to offer some more information for user to.

hello @Bomba , you can refer to the following sample.

Sample data

You can create the following two measures.

top5_group_customer =
VAR _top5 =
    SUMMARIZE (
        TOPN (
            5,
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Group]
                    IN VALUES ( 'Table'[Group] )
                        && [Week] IN VALUES ( 'Table'[Week] )
            ),
            CALCULATE ( SUM ( 'Table'[Value] ) ), DESC
        ),
        [Customer]
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Customer] IN _top5 )
    )

top5_customer =
VAR _top5 =
    SUMMARIZE (
        TOPN (
            5,
            FILTER ( ALLSELECTED ( 'Table' ), [Week] IN VALUES ( 'Table'[Week] ) ),
            CALCULATE ( SUM ( 'Table'[Value] ) ), DESC
        ),
        [Customer]
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Customer] IN _top5 )
    )

Then create two table visuals and put the measure to the visual.

Output 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise · Accepted solution]

If you want to use power query, use the below code

let

    Source = YourDataSource,

    Grouped = Table.Group(Source, {"Group", "Week", "Customer"}, {{"Total Sales", each List.Sum([Sales]), type number}}),

    Ranked = Table.AddColumn(Grouped, "Rank", each Table.AddIndexColumn(Table.Sort(Table.SelectRows(Grouped, each [Group] = _[Group] and [Week] = _[Week]), {"Total Sales", Order.Descending}), "Index", 1, 1)[Index]),

    FilteredTop5 = Table.SelectRows(Ranked, each [Rank] <= 5),

    AllCustomers = Table.Group(Source, {"Customer"}, {{"Total Sales", each List.Sum([Sales]), type number}}),

    AllRanked = Table.AddColumn(AllCustomers, "Rank", each Table.AddIndexColumn(Table.Sort(AllCustomers, {"Total Sales", Order.Descending}), "Index", 1, 1)[Index]),

    Top5All = Table.SelectRows(AllRanked, each [Rank] <= 5)

in

    {FilteredTop5, Top5All}

[shafiz_p · Accepted solution]

Hi @Bomba ,
As I can understand, that you want to find out top 5 customer of each group each week. You need to create 2 measure to achieve this. Rank measure to evaluate rank and then filter only top 5.

I have Product Table, Date Table and Sales Table and relationship is one to many. I want to find out top 5 products by revenue of each product line each week. Similar situation. 

To evaluate rank, you can try below code:

RankProduct = 
RANKX(
    ALLSELECTED(DimProduct[EnglishProductName]),
    [Revenue],
    ,
    DESC,
    DENSE
)

Revenue = Sum of sales.

To find top 5, try below code:

Top 5 = 
IF(
    [RankProduct] <= 5,
    1,
    0
)

Here is the desired output:

To achieve this final result, just select the matrix visual, go to filter pane and place the top 5 measure into filter this visual section and set value is 1 and apply. See image:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz