Sum by person by category: New column

[ARLYS6]

I need to create a column that calculates the sum of responses received by faculty member per college. An example of the data set is below with the Total Received per faculty/college being the column that I am trying to calculate with DAX:

Person               College        Survey         # Received        Total Received per faculty/college  

Faculty A            Medicine    XXXXXX          10                     25

Faculty A            Medicine    XXXXXX           15                    25

Faculty B             Dental       XXXXX              20                   30

Faculty B             Dental        XXXXX             10                   30

Faculty B             Nursing      XXXXX              5                    15

Faculty B             Nursing      XXXXX             10                   15

When I thought I only needed total received by faculty, the dax below worked great:

Total by faculty= CALCULATE (SUM('Table'[# Received]), ALLEXCEPT ('Table', 'Table'[person])) worked great

But then I realized that there are some faculty who teach at multiple colleges and I needed the new column to reflect total received per faculty per college. Any help is appreciated.

[johnt75 · Accepted solution]

You can modify your code slightly to add college to the ALLEXCEPT

Total by faculty =
CALCULATE (
    SUM ( 'Table'[# Received] ),
    ALLEXCEPT ( 'Table', 'Table'[person], 'Table'[College] )
)

[ARLYS6]

Thank you! I wasn't expecting it to be that easy.