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
bcanfield83
New Member

SUMX + FILTER + EARLIER...

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:

UserIDReport Count
asmithABC154
jdoeABC12
cmyersCDE482
asmithCDE41
jdoeCDE45
cmyersDEF24
bjonesDEF2178
ejohnsonDEF23
hhillDEF249
asmithDEF23
mjonesDEF233
jdoeDEF29
pallenDEF277
mjonesHIJ144
jdoeHIJ133
bjonesJKL112
ejohnsonJKL112

 

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:

 

UserIDJob Title
asmithSpecialist
jdoeSupervisor
cmyersAnalyst
bjonesSales Rep
ejohnsonSales Rep
hhillSales Rep
mjonesSpecialist
pallenSupervisor

 

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! 😊

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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.

 

SU18_powerbi_badge

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.

 

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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.

 

SU18_powerbi_badge

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!!

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