cancel
Showing results for
Did you mean:
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 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.

1 ACCEPTED SOLUTION
Super User

There are ties in ranking,

 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 beyond their comprehension! DAX is simple, but NOT EASY!
6 REPLIES 6
Super User
``= CALCULATE( SUM( Table1[net] ), CALCULATETABLE( Table2 ) )``

 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 beyond their comprehension! DAX is simple, but NOT EASY!
New Member

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

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

 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 beyond their comprehension! DAX is simple, but NOT EASY!
New Member

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!

Super User

There are ties in ranking,

 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 beyond their comprehension! DAX is simple, but NOT EASY!
New Member

Thanks a lot @CNENFRNL !!!!!

Announcements

#### CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors