Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |