Forum Discussion
Calculate Transactions Per Order
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
// (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
3 Replies
- Greg_Deckler
Community Champion
Derekp978 - Use ALL or ALLEXCEPT to ignore filter context.
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]))
// (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
