Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I'm attempting to calculate a sum of values in 1 table for each job title in a related table.
I'm assuming I need to incorporate SUMX, FILTER, and EARLIER in some capacity, but I'm struggling a bit with the logic. 😕
There are 2 tables:
tblActivity:
UserID | Report | Count |
asmith | ABC1 | 54 |
jdoe | ABC1 | 2 |
cmyers | CDE4 | 82 |
asmith | CDE4 | 1 |
jdoe | CDE4 | 5 |
cmyers | DEF2 | 4 |
bjones | DEF2 | 178 |
ejohnson | DEF2 | 3 |
hhill | DEF2 | 49 |
asmith | DEF2 | 3 |
mjones | DEF2 | 33 |
jdoe | DEF2 | 9 |
pallen | DEF2 | 77 |
mjones | HIJ1 | 44 |
jdoe | HIJ1 | 33 |
bjones | JKL1 | 12 |
ejohnson | JKL1 | 12 |
The "Count" column simply represents the # of times the listed UserID downloaded that particular Report. Each UserID falls into a specific job title which is defined in this related table:
tblUsers:
UserID | Job Title |
asmith | Specialist |
jdoe | Supervisor |
cmyers | Analyst |
bjones | Sales Rep |
ejohnson | Sales Rep |
hhill | Sales Rep |
mjones | Specialist |
pallen | Supervisor |
There's a "Many-to-one" relationship between tblActivity[UserID] --> tblUsers[UserID]
Thus, for each of the Reports in tblActivity (ABC1, CDE4, DEF2, etc.), I'm attempting to calculate the # of times that the report was downloaded for each respective job title (based upon that user's job title in tblUsers). In some cases, a given report may have only been downloaded by certain users (e.g.: report ABC1 was only downloaded by asmith and jdoe).
So, using Report "DEF2" as an example, my expected values would be:
-Analyst: 4 (cmyers)
-Sales Rep: 230 (bjones 178 + ejohnson 3 + hhill 49)
-Specialist = 36 (mjones 33 + asmith 3)
-Supervisor = 86 (pallen 77 + jdoe 9)
Any help / hints would be greatly appreciated - thank you! 😊
Solved! Go to Solution.
Hi @bcanfield83
1. Place tblUsers[JobTitle] in a table visual
2. Place tblActivity[Report] in a slicer
3. Place this measure in the table visual
Measure = SUM(tblActivity[Count])
4. Select the report you want to look at in the slicer
See it all at work in the attached file.
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @bcanfield83
1. Place tblUsers[JobTitle] in a table visual
2. Place tblActivity[Report] in a slicer
3. Place this measure in the table visual
Measure = SUM(tblActivity[Count])
4. Select the report you want to look at in the slicer
See it all at work in the attached file.
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Thank you!!
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |