Calculating duplicated values on the same column

[Diodio26]

Hello. I´ve been trying to do the following but haven´t been able to. I would appreciate any help.

Project	Member	Value	Value 2
A	Laura	30	1
A	Laura	30	2
A	George	40	4
B	Peter	20	5
C	Peter	20	9
C	George	10	11

I would like for project A, to obtain 30+40 = 70. Instead of getting 30+30+40.

Value 2 was obtained with a merge query and that caused some rows to duplicate.

Thanks in advance.

[v-yingjl · Accepted solution]

Hi @Diodio26 ,

Based on your description, you can create a calculated column like this:

_Value =
VAR tab =
    SUMMARIZE ( 'Table', 'Table'[Project], 'Table'[Member], 'Table'[Value] )
RETURN
    SUMX ( FILTER ( tab, [Project] = EARLIER ( 'Table'[Project] ) ), [Value] )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I wrote these measures

Total = min(Data[Value])

Measure = SUMX(SUMMARIZE(Data,Data[Project],Data[Member],"ABCD",[Total]),[ABCD])

To your visual, drag Project and Measure.

Hope this helps.

[mahoneypat]

You should further modify your query to remove those duplicate rows if you don't need them.  In any case, here is a measure that should get your desired result.

NewMeasure =
VAR vSummary =
    SUMMARIZE (
        Projects,
        Projects[Project],
        Projects[Member],
        "cAvg", AVERAGE ( Projects[Value] )
    )
RETURN
    SUMX (
        vSummary,
        [cAvg]
    )

Regards,

Pat