Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-06-17 211031.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Thanks a lot @CNENFRNL !!!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors