variables virtual tables

[dgdgdg122db]

Hello there,

I am trying to count the number of orders for the customers who has the total order value above 50K. My DAX looks like below, I just do not how to continue. How to pass the "50orders" to my calculation.

Can someone please help.

#Orders (Buyers OrderValue>50k) =

var totalorders=

SUMMARIZE(Orders,

'Orders'[CustomerID],

"TotalSales",SUM(Orders[Order Value EUR]),

"50orders",DISTINCTCOUNT(Orders[Sales document]))

var order50K= FILTER(totalorders,[TotalSales]>50000)

return

[bcdobbs]

Have you tried COUNTROWS ( order50K )

[dgdgdg122db]

Yes and that is incorrect, it will only count the number of customers, (with a total sales amount >50K) not the number of orders. 

[bcdobbs · Accepted solution]

Sorry misunderstood your requirement. Hard to test without some data but I think you want to do something like:

#Orders (Buyers OrderValue>50k) =

VAR Customers50k =
	FILTER (
		VALUES ( 'Orders'[CustomerID] ),
		CALCULATE ( SUM( Orders[Order Value EUR]) ) > 50000
	)
	
RETURN
	CALCULATE (
		DISTINCTCOUNT ( Orders[Sales document] ),
		Customers50k
	)

Effectively generate the list of CustomerIds and then use it as a filter inside calculate.

[dgdgdg122db]

Could you please tell me why the Calculate is needed in this section : CALCULATE ( SUM( Orders[Order Value EUR]) ) > 50000 ?

[bcdobbs]

I'll try!

CALCULATE does a number of things in DAX. It can add/remove filters but it also forces "context transition". This is where a row context gets moved into the filter context for a calculation.

In the code FILTER iterates over each row in the list of CustomerIds. For each row (customer is) we need to calculate total sales so that rows customer id needs to be moved into the filter context. If it wasn't there you'd get the same value for every customer id.

[dgdgdg122db]

Thank you very much @bcdobbs