Top5 customers per group per week

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

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}

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.