Forum Discussion
Solved
variables virtual tables
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
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.
6 Replies
Have you tried COUNTROWS ( order50K )
Yes and that is incorrect, it will only count the number of customers, (with a total sales amount >50K) not the number of orders.
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.
