Unique sum using DAX

[MahiK]

PCode PNAME VALUE VC
CE ABC 100 XX
CE ABC 100
XY MNO 200 YY
XM PQR 100 ZZ
I want total sum for VALUE column it should only sum values for unique PCode,PName and Value combination like it should only sum CE ABC 100 only one time as it is duplicated, VALUE column is coming from table2 and Pcode and PNAme is coming from table1

[MS_Sum]

Hi @MahiK

PTable

Output: 600

Formula:

Total Sum =

SUMX (

    SUMMARIZE (

        'PTable',

        'PTable'[PCode],

        'PTable'[PName],

        'PTable'[Value],

        "Count", CALCULATE (

                    COUNTROWS('PTable'),

                    'PTable'[PCode] = EARLIER('PTable'[PCode]),

                    'PTable'[PName] = EARLIER('PTable'[PName]),

                    'PTable'[Value] = EARLIER('PTable'[Value])

                )

    ),

    IF([Count] = 1, PTable[Value], 'PTable'[Value])

)

Try and see if it works for you. If yes could you please mark it as a solution and give a kudo 🙂

[MahiK]

Thanks but The value column is coming from different table so in summarize we can not have all three columns which is causing the issue

[MS_Sum]

Assuming you are retrieving values based on PCode matching in both tables, if so merging (Left Outer Join) should work and can summarize with the values. If not let me know in what condition the "values" will be fetched.

Total Sum =

SUMX (

    SUMMARIZE (

        'PTable',

        'PTable'[PCode],

        'PTable'[PName],

        'PTable'[QTable.Value],

        "Count", CALCULATE (

                    COUNTROWS('PTable'),

                    'PTable'[PCode] = EARLIER('PTable'[PCode]),

                    'PTable'[PName] = EARLIER('PTable'[PName]),

                    'PTable'[QTable.Value] = EARLIER('PTable'[QTable.Value])

                )

    ),

    IF([Count] = 1, PTable[QTable.Value], 'PTable'[QTable.Value])

)

Let me know if that works with a kudo and marking the suggestion as a solution.

[BA_Pete]

Hi @MahiK ,

You could use a measure something like this:

_sum_PCode_PName_Value =
SUMX(
    yourTable,
    MAX(yourTable[Value])
)

You'd need to make sure you use it with the correct dimensions in a visual, otherwise it may give unexpected results.

Pete