SUMMARIZE giving partial correct result

[TRADER083]

Hi everyone,

I have the following DAX. It's probably not the ideal way of summarizing data but trying to understand why it's not giving the correct results.

1. tem_table is a temp table that filters based on 2 conditions.

2. sum_table1 uses tem_table and group by field1. sum_table1 does not always gives the correct results. Some rows are summed as if no filters were applied, while other rows are summed corrected based on the filter.
3. sum_table2 is a more simple version of what I am trying to achieve and it gives the correct result.

Question: why doesn't sum_table1 behave consistenctly right/wrong?

TEST =
    var tem_table =
                    SELECTCOLUMNS (
                        FILTER (
                            table1,
                            filter1 = "123" && filter2 = "456" --filtering field
                        ),
                        field1, --grouping field
                        "sum1", table1[field2] --summing field
                    )

VAR sum_table1 =
                SUMMARIZE(
                    tem_table,
                    field1,
                    "sum1", sum(table1[field2])
                )

VAR sum_table2 =
                SUMMARIZE(
                    FILTER (
                        table1,
                        filter1 = "123" && filter2 = "456"
                    ),
                    field1,
                    "sum1", sum(table1[field2])
                )

return sum_table1

[Anonymous]

Hi @TRADER083 ,

Based on the description, try to modify the dax formula to the following.

TEST =
VAR tem_table =
    SELECTCOLUMNS (
        FILTER (
            table1,
            filter1 = "123" && filter2 = "456"
        ),
        "field1", field1,
        "sum1", table1[field2]
    )

VAR sum_table1 =
    SUMMARIZE(
        tem_table,
        [field1],
        "TotalSum1", SUMX(tem_table, [sum1])
    )

return sum_table1

View the summarize dax information.

SUMMARIZE function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

sum_table1 is summarizing the field "field2"  which doesn't exist in tem_table as you replaced it with "sum1".

But that's only a part of your problem.  Mandatory read:  https://www.sqlbi.com/articles/all-the-secrets-of-summarize/