CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9714

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

In this example (Calculate ratio of Category Sales to Total Sales): https://learn.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://learn.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 )
    )

Upvotes: 0

Views: 314

Answers (1)

sergiom
sergiom

Reputation: 4887

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 )
    )

Upvotes: 0

Related Questions