Forum Discussion
Create calculated table from two tables
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!
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
2 Replies
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
