Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |