Calculate count of orders by multiple statuses

[ManjunathaEP]

Hi, I have sample data and requirements as mentioned below, please help me out with DAX.

Sample data

Order#	Cust.Name	Y/N?	Amount
1	ABC	Y	12
2	XYZ	N	23
3	HFG	Y	45
4	DGF	Y	656
5	FFG	Y	75
6	ABC	N	67
7	XYZ	N	55
8	YUI	N	44
9	KDF	N	33

Requirements

1. Count of orders for the distinct customer for Y/N? = Y and N. (Result must be 4 for ABC and XYZ every 2 orders)

2. Count of orders for the distinct customer for Y/N? = Y (Result must be 3 for HFG,DGF and FFG each 1 order)

3. Count of orders for the distinct customer for Y/N? = N (Result must be 2 for YUI and KDF each 1 order)

4. Sum of row totals by customer name does not match with the column total

[jameszhang0805]

Based on your data source, and the requirements you described, the result should be :

And  4. Sum of row totals by customer name does not match with the column total
what is this mean? can you give an example?

[ManjunathaEP]

Thank you for putting the table with the expected result and this is what is expected.

Requirement 4: If I get the totals correctly as shown in the result table, that would suffice. 

Thank you for this, it will be good, if I get DAX

[jameszhang0805]

#OrderYandN = 
VAR _TableN = CALCULATETABLE( VALUES( 'Order'[Cust.Name] ), 'Order'[Y/N?] = "N" )
VAR _TableY = CALCULATETABLE( VALUES( 'Order'[Cust.Name] ), 'Order'[Y/N?] = "Y" )
VAR _BothYandN = INTERSECT( _TableY , _TableN )
RETURN COUNTROWS( _BothYandN )

#Orders_Y = 
VAR _TableN =
CALCULATETABLE( VALUES( 'Order'[Cust.Name] ), 'Order'[Y/N?] = "N" )
VAR _TableY = EXCEPT( VALUES( 'Order'[Cust.Name] ) , _TableN )
RETURN CALCULATE( COUNTROWS( 'Order' ) , _TableY ) 

#Orders_N = 
VAR _TableY =
CALCULATETABLE( VALUES( 'Order'[Cust.Name] ), 'Order'[Y/N?] = "Y" )
VAR _TableN = EXCEPT( VALUES( 'Order'[Cust.Name] ) , _TableY )
RETURN CALCULATE( COUNTROWS( 'Order' ) , _TableN )  

[ManjunathaEP]

Hi, Thank you for the reply but the given DAX is calculating the "customer count" instead of the "order count". I expected the order count from the table. It will be good, If I get DAX to calculate distinct order count from given sample data.