cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sum based on other columns

How do I calculate the sum of Hours based on ProjectNum and Assignee Name? 

If I just sum the Hours column, the expected value doubles or more. I also tried creating a new column using sum and allexcept, but didn't work.

Note: Hours is a calculated column. 

 

Data we have:

ProjectNum |  AssigneeName  |  Hours

123                 Ann                       2

123                 Ann                       5

456                 Joe                        3

456                 Joe                        7

456                 Ann                       4

 

Output should be:

ProjectNum |  AssigneeName  |  TotalHours

123                 Ann                       7

456                 Joe                        10

456                 Ann                        4

 

Thanks.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello,

A different column called 'Date' was causing the duplicates. I craeted a calculated column by taking the max of this Date and gave it a value 1 and then applied a page level filter by 1.

 

The formula for max looks like the one below:

MaxDate = IF('Table1'[Date]=CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[column1], 'Table1'[column2])),1,0)

 

After this sum will work for the 'Hours column'. 😄

View solution in original post

7 REPLIES 7
HotChilli
Super User
Super User

Make sure 'ProjectNum' is set to 'Do not summarize'

Anonymous
Not applicable

Hey, thanks. Yes, it is set to Don't summarize. Still didn't work. 😞

Ok, help me and I'll help you. Show me what you have or attach the pbix

Anonymous
Not applicable

Unfortunately, I don't find any option to attach a file.

 

Below is the formula I used for calculating TotalHours:

TotalHours = CALCULATE (
MAX( 'Table'[Hours] ),
ALLEXCEPT( 'Table', 'Table'[ProjectNum], 'Table'[AssigneeName]
))
 
And then I use SUM on the Hours coulmn instead of Don't Summarize. It then doubles the expeced value.
 
In the formula, if I use SUM( 'Table'[Hours] ) intead of MAX( 'Table'[Hours] ), it doubles the value for each row.

A simple explicit measure:

MTotal Hours = SUM(Table[Hours])

added to a table visualisation which has ProjectNum (do not summarize) and Assignee should give you this

 

Annotation 2019-06-20 ProjNum.png

Anonymous
Not applicable

Thank you for this. It should work, but for my data set it is not. The sum seems to double because there are duplicate rows with respect to the Hours Field in the data set.

Anonymous
Not applicable

Hello,

A different column called 'Date' was causing the duplicates. I craeted a calculated column by taking the max of this Date and gave it a value 1 and then applied a page level filter by 1.

 

The formula for max looks like the one below:

MaxDate = IF('Table1'[Date]=CALCULATE(MAX('Table1'[Date]),ALLEXCEPT(Table1,'Table1'[column1], 'Table1'[column2])),1,0)

 

After this sum will work for the 'Hours column'. 😄

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!