Summing the max values correctly

[SeymaKalay]

I am trying to sum up totals in powerbi table using the first code below. Eventhough the numbers are appering correctly row by row. I am seeing wrong sumx().

 SUMX(SUMMARIZE(
            FILTER(
                tablename,
                tablename[col1] > 0 &&
                tablename[col2] <> "text" &&
                tablename[col3] <> "text" &&
                LEFT(tablename[col4],2) = "ab"
            ),
            tablename[IDcol], 
            "Correct", MAX(tablename[Amount]),  "Correct")

Measure = 
     SUMMARIZE(
            FILTER(
                tablename,
                tablename[col1] > 0 &&
                tablename[col2] <> "text" &&
                tablename[col3] <> "text" &&
                LEFT(tablename[col4],2) = "ab"
            ),
            tablename[IDcol], 
            "Correct", MAX(tablename[Amount]),
    
            "Wrong", CALCULATE( SUMX(tablename, 
                                     tablename[Amount]),
                                        KEEPFILTERS(tablename[col1] > 0 &&
                                                    tablename[col1] > 0 &&
                                                    tablename[col2] <> "text" &&
                                                    tablename[col3] <> "text" &&
                                                    LEFT(tablename[col4],2) = "ab" ) )
        )

I am adding a screenshot of power query editor.

[v-saisrao-msft]

Hi @SeymaKalay,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @SeymaKalay,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @SeymaKalay,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @SeymaKalay,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @Jai-Rathinavel  for your input on this issue. 
 
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, I have used it as sample data on my end and successfully implemented it.   

Dax Measure:  

Correct Total = 
SUMX(
    SUMMARIZE(
        FILTER(
            AnswersTable,
            AnswersTable[col1] > 0 &&
            AnswersTable[col2] <> "text" &&
            AnswersTable[col3] <> "text" &&
            LEFT(AnswersTable[col4], 2) = "ab"
        ),
        AnswersTable[IDcol],
        "MaxAmt", MAX(AnswersTable[Amount])
    ),
    [MaxAmt]
)


Wrong Total = 
CALCULATE(
    SUM(AnswersTable[Amount]),
    AnswersTable[col1] > 0,
    AnswersTable[col2] <> "text",
    AnswersTable[col3] <> "text",
    LEFT(AnswersTable[col4], 2) = "ab"
)

Output: 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
 
Thank you.

[maruthisp]

Hi SeymaKalay,
I tried to implement a solution for your problem. Please find the attached pbix file.
Summing the max values correctly.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Jai-Rathinavel]

Hi @SeymaKalay 

1. Try the below DAX and see if that helps. 

Measure New =
SUMMARIZE(
    FILTER(
        tablename,
        tablename[col1] > 0 &&
        tablename[col2] <> "text" &&
        tablename[col3] <> "text" &&
        LEFT(tablename[col4], 2) = "ab"
    ),
    tablename[IDcol],
    "Correct", MAX(tablename[Amount]),
    "Wrong", CALCULATE(
        SUM(tablename[Amount]),
        KEEPFILTERS(
            tablename[col1] > 0 &&
            tablename[col2] <> "text" &&
            tablename[col3] <> "text" &&
            LEFT(tablename[col4], 2) = "ab"
        )
    )
)

2. Alternatively, you can also create Two separate measures for Correct and Wrong as below

Correct = 
CALCULATE(
    MAX(tablename[Amount]),
    tablename[col1] > 0,
    tablename[col2] <> "text",
    tablename[col3] <> "text",
    LEFT(tablename[col4], 2) = "ab"
)

Wrong = 
CALCULATE(
    SUM(tablename[Amount]),
    tablename[col1] > 0,
    tablename[col2] <> "text",
    tablename[col3] <> "text",
    LEFT(tablename[col4], 2) = "ab"
)

Thank,

Jai Rathinavel | LinkedIn