How to combine lookup value with sumif

[o59393]

Hi all

I need to have the sumif from another table without duplicating the values.

In the example below a table called 'Template' has repeated the column Activity ID:

And another table called 'Summary' which should receive the sum of the hours of the table template without duplicates.

The column both tables have in column is 'merged'.

I am using the following dax:

Non Duplicate Hours Sum IF = 

CALCULATE (
    SUM(Template[Hours by Year]),
    FIRSTNONBLANK ( Template[Activity ID], 1 ),
    FILTER ( ALL ( Template ), Template[Merged] = 'Tech Collab'[Merged] )
)

However the result is not correct.

The result should be the sum of the non duplicated hours, which should be 1158 hours:

Any idea on how to fix my dax?

Thanks.

[Ahmedx]

pls try this

[o59393]

Hi @Ahmedx 

would it be possible to have it as a calculated column instead of a measure?

thank you!

[Ahmedx]

pls try this

[o59393]

Hi @Ahmedx 

Thank you, almost there. The only thing is that the table summary should not have the activity id.

Table Summary should just be merged column and your calculated column. Something like this circled:

Would it be possible?

Thanks.

[Ahmedx]

pls try this

[o59393]

Hi @Ahmedx 

When I use the formula in my real table I get a lot of duplicates for different Merged values in the table summary.

Can you please copy and paste the attached values below in your data source?

Test_Data.xlsx

The attach the xls contains the soution desired in the column 'hours'. Please notice I did the calculation manually just to represent the actual solution.

Thanks

[Ahmedx]

pls try htis

[Ahmedx · Accepted solution]

and try this

[o59393]

Perfect @Ahmedx 

Thank you for your patience and help!

Regards.

[Ahmedx]

By the way, the "DISTINCT" function is superfluous, you can remove it, it will not affect the result

[az38]

@o59393 

still not clear, sorry

for data sample like this

I've got result like this

[o59393]

It's ok.

Can you please use these 2 tables:

Template:

Activity ID	Hours by Year	Merged
8	117	DSTO
8	117	DSTO
8	117	DSTO
8	117	DSTO
8	117	DSTO
8	117	DSTO
8	117	DSTO
1	386.1	DSTO
1	386.1	DSTO
1	386.1	DSTO
1	386.1	DSTO
1	386.1	DSTO
1	386.1	DSTO
1	386.1	DSTO
13	70.2	DSTO
13	70.2	DSTO
7	117	DSTO
7	117	DSTO
6	117	DSTO
6	117	DSTO
14	70.2	DSTO
14	70.2	DSTO
9	163.8	DSTO
9	163.8	DSTO
9	163.8	DSTO
9	163.8	DSTO
9	163.8	DSTO
9	163.8	DSTO
10	117	DSTO
10	117	DSTO

And Summary:

Merged

DSTO

Then create the relationship of 1:M from Merged to Merged.

Finally the table merged is where you should add your calculated column.

Hope this helps.

Thanks!

[az38]

Hi @o59393 

it' s not clear your full model and task but you can try a measure

Non Duplicate Hours Sum IF = 
var _t = DISTINCT(Template[Hours By Year])
RETURN
CALCULATE(SUMX(_t, [Hours By Year]))

[o59393]

Hi @az38 

Thanks for your reply.

Can Activity ID be added to your dax? This is because for example 2 different activivities could have the same hours (for example 10 and 10 hours).

With your dax it would only sum 10, however it should be 20 as the activitiy Id's are different.

In my dax I also put the lookup function because the table Summary should lookup the sumif from the table template.

The table summary that I already exists should have the measure:

Merged	Non Duplicate Hours Sum IF
DSTO	1158.3

 Hope Im more clear.

Thanks.