Forum Discussion
gumis_rulez
Helper I
Helper ICreating Summarize and referencing it in the same function to sum up values
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
)
)
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] )
2 Replies
- Jihwan_Kim
Super User
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] )
