Forum Discussion

MahiK's avatar
MahiK
Regular Visitor
3 years ago

Unique sum using DAX

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

Need Help

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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

  • MS_Sum's avatar
    MS_Sum
    Frequent Visitor
    3 years ago

    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's avatar
      MahiK
      Regular Visitor
      3 years ago

      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's avatar
        MS_Sum
        Frequent Visitor
        3 years ago

        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.