Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I would like to create a measure which shows top3 users by name based on their summarized activity values from 'User table' and 'User Activity table'. The filter connection between the tables is 'User table' 1 - many 'User activity table'
The measure result should be a calcualted tabe with two columns: User table [Name], and sum of [Quantity] from User Activity table.
I tried to do it with CALCULATEDTABLE, TOPN and ADDCOLUMN functions, but I can not figure out the correct measure definition. Could you help me to solve this? Thank you very much!
Solved! Go to Solution.
@EnderWiggin Try below. PBIX is attached below signature.
Username Sum Quantity Top 3 =
VAR __BaseTable =
SUMMARIZE(
'User Activity Table',
[UserId],
"Sum Quantity", SUM('User Activity Table'[Quantity])
)
VAR __Table =
ADDCOLUMNS(
__BaseTable,
"Username", MAXX(FILTER('User Table', [Id] = [UserId]),[Name]),
"Rank", RANKX(__BaseTable, [Sum Quantity],,DESC)
)
VAR __Result =
SELECTCOLUMNS(
FILTER(__Table, [Rank] <= 3),
"Username", [Username],
"Sum Quantity", [Sum Quantity]
)
RETURN
__Result
@Greg_Deckler thank you very much for your detailed and clear solution, this is what I wanted.
@EnderWiggin Try below. PBIX is attached below signature.
Username Sum Quantity Top 3 =
VAR __BaseTable =
SUMMARIZE(
'User Activity Table',
[UserId],
"Sum Quantity", SUM('User Activity Table'[Quantity])
)
VAR __Table =
ADDCOLUMNS(
__BaseTable,
"Username", MAXX(FILTER('User Table', [Id] = [UserId]),[Name]),
"Rank", RANKX(__BaseTable, [Sum Quantity],,DESC)
)
VAR __Result =
SELECTCOLUMNS(
FILTER(__Table, [Rank] <= 3),
"Username", [Username],
"Sum Quantity", [Sum Quantity]
)
RETURN
__Result
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
129 | |
78 | |
76 | |
60 | |
53 |
User | Count |
---|---|
164 | |
86 | |
68 | |
68 | |
58 |