Total showing wrong

[ZaraTyf1983]

Hi everyone,
I have a DAX formula that's showing incorrect total values in the table visual. I understand the reason behind it, but I'm unsure how to fix it.

   MPY Reward Exc. GST =

 IF (

        AND ( [#MPY] > 0, [#MPY] < 150000 ),

        0,

        IF (

            AND ( [#MPY] >= 150000, [#MPY] < 400000 ),

             [#MPY] * 0.0227,

            IF (

                AND ( [#MPY] >= 400000, [#MPY] < 700000 ),

                [#MPY] * 0.0273,

                IF (

                    AND ( [#MPY] >= 700000, [#MPY] < 1000000 ),

                    [#MPY] * 0.0318,

                    IF (

                        AND ( [#MPY] >= 1000000, [#MPY] < 3000000 ),

                        [#MPY] * 0.0364,

                        [#MPY] * 0.0409

                    )

                )

            )

        )

    )

)

The expected total should be $1,186,662, but it's currently showing $1,381,418.

Thanks in advance!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SacheeTh · Accepted solution]

Can't say anything without your data, but by the look of it it seems the issue with incorrect total values in a table visual is common when using DAX calculations, especially with IF conditions like yours. This happens because DAX treats the "total" row differently than the row context, leading to undesired results.

To fix it, you can modify your measure to ensure that the total calculation is correct. You can use the SUMX function, which will iterate over the rows of your table to calculate the correct total, taking the row-level logic into account.

Here’s how you can modify your measure:

MPY Reward Exc. GST =
SUMX (
    VALUES ( YourTableName[#MPY] ),
    IF (
        AND ( [#MPY] > 0, [#MPY] < 150000 ),
        0,
        IF (
            AND ( [#MPY] >= 150000, [#MPY] < 400000 ),
            [#MPY] * 0.0227,
            IF (
                AND ( [#MPY] >= 400000, [#MPY] < 700000 ),
                [#MPY] * 0.0273,
                IF (
                    AND ( [#MPY] >= 700000, [#MPY] < 1000000 ),
                    [#MPY] * 0.0318,
                    IF (
                        AND ( [#MPY] >= 1000000, [#MPY] < 3000000 ),
                        [#MPY] * 0.0364,
                        [#MPY] * 0.0409
                    )
                )
            )
        )
    )
)

Here, VALUES ( YourTableName[#MPY] ) part ensures that the calculation is performed row by row.
This approach will calculate the total as the sum of individual row results, rather than applying the measure logic to an aggregate context, which leads to incorrect results.

I know this solution doesn't fully address your problem, please provide more context