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

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors