Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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'. 😄
Make sure 'ProjectNum' is set to 'Do not summarize'
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
Unfortunately, I don't find any option to attach a file.
Below is the formula I used for calculating TotalHours:
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
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.
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'. 😄
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |