SUMX + FILTER + EARLIER...

[bcanfield83]

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

[AlB · Accepted 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.

[bcanfield83]

Thank you!!