Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Derekp978
Frequent Visitor

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 IDTransaction IDTransactions in OrderFilter 1Filter 2Filter N
1900013YA...
1900023YB...
1900033NA...
2900042YC...
290052YA...

 

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 IDTransaction IDTransactions in OrderFilter 1Filter 2Filter NCALC TransactionCALC Orders 
1900013YA...31
1900023YB...  
1900033NA...  
2900042YC...21
290052YA...  

 

 

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

Order IDTransaction IDTransactions in OrderFilter 1Filter 2Filter NCALC TransactionCALC Orders 
1900013YA...  
1900023YB...  
1900033NA...31
2900042YC...  
290052YA...  

 

 

Thanks in advance

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

// (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

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@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
Not applicable

// (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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.