topic Sum by person by category: New column in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2868493#M92489 <P>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 <STRONG>Total Received per faculty/college</STRONG>&nbsp;being the column that I am trying to calculate with DAX:</P><P>&nbsp;</P><P><STRONG>Person&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;College&nbsp; &nbsp; &nbsp; &nbsp; Survey&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# Received&nbsp; &nbsp; &nbsp; &nbsp; Total Received per faculty/college</STRONG>&nbsp;&nbsp;</P><P>Faculty A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Medicine&nbsp; &nbsp; XXXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25</P><P>Faculty A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Medicine&nbsp; &nbsp; XXXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Dental&nbsp; &nbsp; &nbsp; &nbsp;XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Dental&nbsp; &nbsp; &nbsp; &nbsp; XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Nursing&nbsp; &nbsp; &nbsp; XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Nursing&nbsp; &nbsp; &nbsp; XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15</P><P>&nbsp;</P><P>When I thought I only needed total received by faculty, the dax below worked great:</P><P>&nbsp;</P><P>Total by faculty= CALCULATE (SUM('Table'[# Received]), ALLEXCEPT ('Table', 'Table'[person])) worked great</P><P>&nbsp;</P><P>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.</P> Thu, 27 Oct 2022 15:13:17 GMT ARLYS6 2022-10-27T15:13:17Z Sum by person by category: New column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2868493#M92489 <P>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 <STRONG>Total Received per faculty/college</STRONG>&nbsp;being the column that I am trying to calculate with DAX:</P><P>&nbsp;</P><P><STRONG>Person&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;College&nbsp; &nbsp; &nbsp; &nbsp; Survey&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# Received&nbsp; &nbsp; &nbsp; &nbsp; Total Received per faculty/college</STRONG>&nbsp;&nbsp;</P><P>Faculty A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Medicine&nbsp; &nbsp; XXXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25</P><P>Faculty A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Medicine&nbsp; &nbsp; XXXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Dental&nbsp; &nbsp; &nbsp; &nbsp;XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Dental&nbsp; &nbsp; &nbsp; &nbsp; XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Nursing&nbsp; &nbsp; &nbsp; XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15</P><P>Faculty B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Nursing&nbsp; &nbsp; &nbsp; XXXXX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15</P><P>&nbsp;</P><P>When I thought I only needed total received by faculty, the dax below worked great:</P><P>&nbsp;</P><P>Total by faculty= CALCULATE (SUM('Table'[# Received]), ALLEXCEPT ('Table', 'Table'[person])) worked great</P><P>&nbsp;</P><P>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.</P> Thu, 27 Oct 2022 15:13:17 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2868493#M92489 ARLYS6 2022-10-27T15:13:17Z Re: Sum by person by category: New column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2868689#M92497 <P>You can modify your code slightly to add college to the ALLEXCEPT</P> <LI-CODE lang="markup">Total by faculty = CALCULATE ( SUM ( 'Table'[# Received] ), ALLEXCEPT ( 'Table', 'Table'[person], 'Table'[College] ) ) </LI-CODE> Thu, 27 Oct 2022 16:10:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2868689#M92497 johnt75 2022-10-27T16:10:47Z Re: Sum by person by category: New column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2871517#M92671 <P>Thank you! I wasn't expecting it to be that easy.</P> Fri, 28 Oct 2022 17:35:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sum-by-person-by-category-New-column/m-p/2871517#M92671 ARLYS6 2022-10-28T17:35:01Z