Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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!
Solved! Go to 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
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.
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.
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 @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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.