cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBIFX200
New Member

Calculated table based on 2 different tables.

Hello everyone, 

 

Newbie here! Any help would be very much appreciated!

 

Table 1 (unique ID)

Net amount based on uniques ID

ID net
1200
10-300
8-60
9100
12150
2560
23-60
1150
13-90
260-500
630500
30622
38-900
50-86
9550
533

 

Table 2

each Employee or Team could have multiple IDs.

 

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

 

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 Teamnet
Team stars-190
Adam-60
josh100
Team PBI150
clara60
james-60
Team python50
abe-500
jessica500
Team winners622
Cain-900
Florence-86
Oliver50
Elias33

 

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

 

Thanks in advance

1 ACCEPTED SOLUTION

There are ties in ranking,

Screenshot 2021-06-18 005008.png

View solution in original post

6 REPLIES 6
CNENFRNL
Super User III
Super User III

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

Screenshot 2021-06-17 211031.png

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

PBIFX200_0-1623958306981.png

 

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

Screenshot 2021-06-17 224458.png

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 

PBIFX200_0-1623965049292.png

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!

 

unnamed.png

 

 

There are ties in ranking,

Screenshot 2021-06-18 005008.png

View solution in original post

Thanks a lot @CNENFRNL !!!!!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors