topic Re: Create calculated table from two tables in Desktop

Create calculated table from two tables

EnderWiggin — Sun, 02 Apr 2023 15:10:11 GMT

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!

Re: Create calculated table from two tables

Greg_Deckler — Sun, 02 Apr 2023 15:53:04 GMT

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

Re: Create calculated table from two tables

EnderWiggin — Sun, 02 Apr 2023 20:29:27 GMT

Greg_Deckler thank you very much for your detailed and clear solution, this is what I wanted.