Calculate Transactions Per Order

[Derekp978]

All - I have a simple data set that looks like the following - and we are looking for Avg Transactions Per Order (For all orders that remain unfiltered completely)

Order ID	Transaction ID	Transactions in Order	Filter 1	Filter 2	Filter N
1	90001	3	Y	A	...
1	90002	3	Y	B	...
1	90003	3	N	A	...
2	90004	2	Y	C	...
2	9005	2	Y	A	...

So accross this data set - the following DAX was originally built:

Avg Transactions per Order := DIVIDE(COUNT([TRANSACTION ID]),DISTINCTCOUNT([Order ID]))

Without any filters this yeilds, correctly: 5/2 = 2.5

This issue is if there are any filters - assume [Filter 1] = 'Y' - then:  4/2 = 2

But I still want 2.5 because 2 orders were included - and in total there were 5 transactions in those orders.  I have currently changed the code to the below, but was looking for something were I don't need to add a condition every time a new Filter enteres my Data Set.

Avg Transactions per Order := DIVIDE(CALCULATE(COUNT([TRANSACTION ID]), ALL([FILTER 1],[FILTER 2], [FILTER N])),DISTINCTCOUNT([Order ID]))

Since I already have the Order Transaction Count can I use some summary/first/over function to get just a singular value per Order 1 to calculate - see below:

For Filter 1 = Y then (3 + 2) / (1 + 1) = 2.5

Order ID	Transaction ID	Transactions in Order	Filter 1	Filter 2	Filter N	CALC Transaction	CALC Orders
1	90001	3	Y	A	...	3	1
1	90002	3	Y	B	...
1	90003	3	N	A	...
2	90004	2	Y	C	...	2	1
2	9005	2	Y	A	...

For Filter 1 = N then (3 + 0) / (1 + 0) = 3

Order ID	Transaction ID	Transactions in Order	Filter 1	Filter 2	Filter N	CALC Transaction	CALC Orders
1	90001	3	Y	A	...
1	90002	3	Y	B	...
1	90003	3	N	A	...	3	1
2	90004	2	Y	C	...
2	9005	2	Y	A	...

Thanks in advance

[Greg_Deckler]

@Derekp978 - Use ALL or ALLEXCEPT to ignore filter context.

[Derekp978]

@Greg_Deckler 

That was my thought but when I applied it it didn't work - if I want to IGNORE these three filters is this correct?

Avg Transactions per Order := DIVIDE(CALCULATE(COUNT([TRANSACTION ID]), ALL([FILTER 1],[FILTER 2], [FILTER N])),DISTINCTCOUNT([Order ID]))

[Anonymous · Accepted solution]

// (ABS) means "absolute", so
// that you know filters do not
// affect this number.
[Avg Tx Per Order (ABS)] =
var __txCount = 
	CALCULATE(
		// Since the table T has a
		// unique column of transactions...
		COUNTROWS( T ),
		VALUES( T[OrderID] ),
		ALL( T )
	)
var __orderCount =
	DISTINCTCOUNT( T[OrderID] )
var __result =
	DIVIDE(
		__txCount,
		__orderCount
	)
return
	__result