Solved: Re: Calculate sum $ for each project based on valu...

reachkrs · ‎09-15-2022

Hi

The data I have is as shown in the image. The data has Project ID, Project Number, Task Number, $, Hour 1 & Hour 2.

Hour 1 & hour 2 is available only for 1st task number or open task only but $ is for each task.

I need to sum $ if the project has either hour 1 or hour 2.

I tried using the following measure

Order $ with hours = calculate(sum('Projects'[$]),filter('Projects',sum('Projects'[Hour 1])>=1))

But this sums only the rows only where I have hour 1 (i.e. $28200). But I need a measure to return the correct value of $63650

Result

Project ID	Project Number	$	Hours 1	Hours 2
XYZ123	1234567890	$9,450	85	15
ABC789	8976543210	$17,500	0	150
CBA567	2134567899	$15,000	125	250
BCA321	9876543219	$9,700	125	150
BCA456	9876543219	$12,000	850	0

Regards

Satheesh

reachkrs · ‎09-28-2022

I could achieve the desired result using the Summarize function.

calculate(sum('Projects'[$]),filter(SUMMARIZE('Projects','Projects'[Project ID],"SH",[Hours]),[SH]>0))

So this post can be closed.

View solution in original post

v-henryk-mstf · ‎09-15-2022

Hi @reachkrs ,

Why the following data does not satisfy the conditions (what is the basis for your specific grouping).

Below is my test:

Column =
VAR a =
    CALCULATE (
        SUM ( Projects[Total Hour] ),
        FILTER (
            ALL ( Projects ),
            Projects[Project Number] = EARLIER ( Projects[Project Number] )
        )
    )
RETURN
    IF ( a > 0, 1, 0 )



M_ =
CALCULATE (
    SUM ( Projects[$] ),
    FILTER (
        ALL ( Projects ),
        MAX ( Projects[Column] ) = 1
            && Projects[Project Number] = MAX ( Projects[Project Number] )
    )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

reachkrs · ‎09-16-2022

Hi Henry,

Thank you for sharing the code. It is showing results. No error messages.

Let me explain my requirement in detail. Project ID is a project management system tool reference that can be linked to

a single project number with multiple or single task number(s)
multiple project number with multiple or single task number(s)

$ is associated with each of the task number but hours are at the Project ID level. So the data what I get from the project management system has hours associated with a single task number because if it repeats for each task then it would result in incorrect hours for a project. It should not be hours multiplied by task.

Considering this I wanted to calculate sum of $ for a project if it has hours associated with it but irrespective of number of tasks under a project. Do not calculate sum $ if does not have any hours associated with it.

I made small correction to the data to make it clear. Sorry earlier post had some data discrepancies.

Project ID & Project Number

ABC788, 8976543222 : $0, correct

ABC789, 8976543210 : $17500 correct. it has hours (150)
BCA321, 9876543219 : should be $9700 ($1200 + $1500 + $7000) but it is showing $33700. It has hours (275)
BCA456, 9876543219 : should be $12000 ($4000 + $8000) but it is showing $33700. It has hours (850)
BCA322, 9876543219 : should be $0 but it is showing $33700. It does not have hours
CBA567, 2134567899 : $15000 correct. It has hours (375)
XYZ123, 1234567890 : $9450, correct. It has hours (100)
XYZ124, 7891234567 : $0, correct. It does not have hours