topic Re: Calculated table based on 2 different tables. in DAX Commands and Tips

Calculated table based on 2 different tables.

PBIFX200 — Thu, 17 Jun 2021 18:50:08 GMT

Hello everyone,

Newbie here! Any help would be very much appreciated!

Table 1 (unique ID)

Net amount based on uniques ID

ID	net
1	200
10	-300
8	-60
9	100
12	150
25	60
23	-60
11	50
13	-90
260	-500
630	500
30	622
38	-900
50	-86
95	50
5	33

Table 2

each Employee or Team could have multiple IDs.

ID	Employee or team
1	Team stars
10	Team stars
8	Adam
9	josh
12	Team PBI
25	clara
23	james
11	Team python
13	Team stars
260	abe
630	jessica
30	Team winners
38	Cain
50	Florence
95	Oliver
5	Elias
1	Team stars
10	Team stars
55	Nancy
500	Team Venus
39	taylor
25	Frank
69	abe
11	team python
76	clark
66	stephanie
625	steven
30	Team winners
44	sarah
98	mary
22	jacob
6	sven

Table 3 ( the result)

The result I'm seeking is an aggregated net amounts by unique (employee or team), as a Table in Data tab not in ~~Report tab~~ in Power bi.

FYI, I have managed to get the below result in the report tab but couldn't replicate the table in the Data tab.

Employee or Team	net
Team stars	-190
Adam	-60
josh	100
Team PBI	150
clara	60
james	-60
Team python	50
abe	-500
jessica	500
Team winners	622
Cain	-900
Florence	-86
Oliver	50
Elias	33

Relationship between Table 1 and Table 2 ( one to many, 1---->----* )oneway.

Thanks in advance

Re: Calculated table based on 2 different tables.

CNENFRNL — Thu, 17 Jun 2021 19:11:21 GMT

= CALCULATE( SUM( Table1[net] ), CALCULATETABLE( Table2 ) )

Re: Calculated table based on 2 different tables.

PBIFX200 — Thu, 17 Jun 2021 19:31:30 GMT

Thank you so much CNENFRNL for the quick reply. I'm looking for the same result but as a table in The Data tab not in report tab. I was able to the same thing. The challenge here is to Create a New table from 2 different tables

Re: Calculated table based on 2 different tables.

CNENFRNL — Thu, 17 Jun 2021 20:46:20 GMT

Table3 = SUMMARIZECOLUMNS( Table2[Employee or team], Table2, "Sum", SUM( Table1[net] ) )

Re: Calculated table based on 2 different tables.

PBIFX200 — Thu, 17 Jun 2021 21:29:27 GMT

Thank so much CNENFRNL for your reply, I don't want to take advantage of your kindness. but if I want to build on your first reply

I want to create a slicer with 2 bottons (top 5 bottom 5) when I select top 5 the green part will be selected and when I select bottom 5 the red part will be selected.

by the way, the below table is the same as the one right above but ranked this time!

if you could help with this I will be very grateful. whenever you are free! thanks again!

Re: Calculated table based on 2 different tables.

CNENFRNL — Thu, 17 Jun 2021 22:51:16 GMT

There are ties in ranking,

Re: Calculated table based on 2 different tables.

PBIFX200 — Sat, 19 Jun 2021 01:24:07 GMT

Thanks a lot CNENFRNL !!!!!