What is the difference between using ALL in SUMX vs CALCULATE?

Question

In this example (Calculate ratio of Category Sales to Total Sales): https://docs.microsoft.com/en-us/dax/all-function-dax#example-1

Formula is:

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / SUMX(
        ALL( ResellerSales_USD ),
        ResellerSales_USD[SalesAmount_USD]
    )

In this example (Calculate Ratio of Product Sales to Total Sales Through Current Year): https://docs.microsoft.com/en-us/dax/all-function-dax#example-2

Formula is:

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / CALCULATE(
        SUM( ResellerSales_USD[SalesAmount_USD] ),
        ALL( DateTime[CalendarYear] )
    )

In the 2nd example why can we not use SUMX ALL similar to example 1 to remove the calendar year filter? Example:

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / SUMX(
        ALL( DateTime[CalendarYear] ),
        ResellerSales_USD[SalesAmount_USD]
    )

Similarly, could 1st example be re-written using CALCULATE as:

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / CALCULATE(
        SUM( ResellerSales_USD[SalesAmount_USD] ),
        ALL( ResellerSales_USD )
    )

sergiom sergiom · Accepted Answer · 2021-01-13T08:16:32

The code

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / SUMX(
        ALL( DateTime[CalendarYear] ),
        ResellerSales_USD[SalesAmount_USD]
    )

would not work, since the SUMX at the donominator is iterating over the column DateTime[CalendarYear], therefore no row context exists to make ResellerSales_USD[SalesAmount_USD] column accessible. Also, the relationship between Date and ResellerSales is one to many, it wouldn't be possible to use RELATED, but RELATEDTABLE and an aggregator would be required, like for instance

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / SUMX(
        ALL( DateTime[CalendarYear] ),
        SUMX( RELATEDTABLE(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])
    )

This one instead is equivalent to the first example

=
SUMX(
    ResellerSales_USD,
    ResellerSales_USD[SalesAmount_USD]
)
    / CALCULATE(
        SUM( ResellerSales_USD[SalesAmount_USD] ),
        ALL( ResellerSales_USD )
    )

What is the difference between using ALL in SUMX vs CALCULATE?

1 Answers