Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Bomba
New Member

Top5 customers per group per week

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!

3 ACCEPTED SOLUTIONS
shafiz_p
Super User
Super User

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:

shafiz_p_0-1730194150751.png

 

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:

shafiz_p_1-1730194281386.png

 

 

Hope this helps!!

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

 

 

Best Regards,
Shahariar Hafiz

View solution in original post

Omid_Motamedise
Super User
Super User

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}

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_2-1730253229236.png

 

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 

vxinruzhumsft_3-1730253302239.png

 

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.

 

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_2-1730253229236.png

 

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 

vxinruzhumsft_3-1730253302239.png

 

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
Super User
Super User

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
Super User
Super User

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:

shafiz_p_0-1730194150751.png

 

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:

shafiz_p_1-1730194281386.png

 

 

Hope this helps!!

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

 

 

Best Regards,
Shahariar Hafiz

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors