Creating Summarize and referencing it in the same function to sum up values

[gumis_rulez]

Hi,

I am trying to make a summarize table (summarizing customers number of orders and total value of those orders) and then would like to refer to it in SUM function to sum sales only those customers who have number of orders less or equal 3. The below code returns total sales of all customers - like a filter does not work. What am I doing wrong?

NewCustomersSales =

VAR CustomerOrders =

    SUMMARIZE(

        Orders,

        Orders[CompanyName],

        "OrdersCount", DISTINCTCOUNT(Orders[OrderID]),

        "TotalSales", SUM(Orders[Sales])

    )

RETURN

    CALCULATE(

        SUMX(

            CustomerOrders,

            [TotalSales]

        ),

        FILTER(

            CustomerOrders,

            [OrdersCount] <= 3

        )

    )

[Jihwan_Kim · Accepted solution]

Hi,

Please try something like below whether it suits your requirement.

NewCustomersSales =
VAR CustomerOrders =
    SUMMARIZE (
        Orders,
        Orders[CompanyName],
        "OrdersCount", CALCULATE ( DISTINCTCOUNT ( Orders[OrderID] ) ),
        "TotalSales", CALCULATE ( SUM ( Orders[Sales] ) )
    )
RETURN
    SUMX ( FILTER ( CustomerOrders, [OrdersCount] <= 3 ), [TotalSales] )

[lbendlin]

NewCustomersSales =
VAR CustomerOrders =
    SUMMARIZE(
        Orders,
        [CompanyName],
        "OrdersCount", DISTINCTCOUNT([OrderID]),
        "TotalSales", SUM([Sales])
    )
RETURN
    SUMX(
        FILTER(
            CustomerOrders,
            [OrdersCount] <= 3
        ),
            [TotalSales]
     )