Forum Discussion

SeymaKalay's avatar
SeymaKalay
Frequent Visitor
1 year ago
Solved

Summing the max values correctly

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( ...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    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.

Jai-Rathinavel's avatar
Jai-Rathinavel
Icon for Super User rankSuper User
1 year ago

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