Calculate the sum of a measure based on the value of another measure

[andymanu]

Hi ,

I got a data set as shown below, (The data set loaded to qlik sense app)

Transaction Date	Division_ID	Service Hours	Actual Cost
2/04/2018	A	100
15/04/2018	B	120
20/04/2018	C	110
25/04/2018	D	130
1/05/2018	A	115	15000
10/05/2018	B	120	17000
12/05/2018	C	110	13500
1/05/2018	D	100	12000
5/06/2018	A	105	11000
15/06/2018	B	135	20000
5/06/2018	C	125	18000
25/06/2018	D	140	22500
15/07/2018	A	150
17/07/2018	B	125
10/07/2018	C	130
1/07/2018	D	40

Output table:I want to compare the service hours against the actual Cost for each division. The output should looks as below,

Division_ID	Service Hours	Actual Cost
A	220	26000
B	255	37000
C	235	31500
D	240	34500

Thus, the sum of each division's service hours and actual costs required to be calculated.

Note

The sum of Service hours needed to be calculated only if a respective actual cost is available.

I tried using Aggr function and other simple expressions such as,

If([Actual Cost]>0, Sum([Service Hours])) and Aggr(If(Sum([Actual Cost]))>0,[Service Hours]).

But none worked. Could anyone please help me with this regard?

Thanking you.

Kind regards,

Andy

[tresesco]

Try using Sum(If(.... instead of If(..Sum(..

Straight table:

Dim 1: Division_ID

Exp 1: Sum(If([Actual Cost]>0, [Service Hours]))

Exp 2: Sum([Actual Cost])

You might have to suppress the null values in dimension as well.

[andymanu]

Hi Tresesco,

Thanks for the reply. Actually, my real data set is a different and a large one.

Apparently I have tried the method you said also where I have got the correct answer but have failed to select the entire data set when performing the manual testing.

Really, sorry about that.

Anyway, thank you again.

Kind regards,

Andy

[jmmolero · Accepted solution]

Hi Andy,

You can create in your data model a new field based on "Service Hours"  that take value only if "[Actual Cost] > 0":

LOAD Service Hours,

Actual Cost

if([Actual Cost] > 0 ,[Service Hours],0) as [Service Hours with Actual Cost]

...

then your expression is:

Sum([Service Hours with Actual Cost])

[andymanu]

Hi Jose,

I really like the method you have approached the issue.

Yes, it works for the given data set, however, it does not work accurately when there are "0" values for the "Service Hours".

Thanks.

Regards,

Andy