Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
= 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 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
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 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
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!
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |