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

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

1 ACCEPTED SOLUTION
Super User

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.

2 REPLIES 2
Super User

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.

New Member

Thank you!!

Announcements

#### 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.

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

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