Forum Discussion

Derekp978's avatar
Derekp978
Frequent Visitor
6 years ago
Solved

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 Tr...
  • Anonymous's avatar
    Anonymous
    6 years ago
    // (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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Derekp978 - Use ALL or ALLEXCEPT to ignore filter context.

  • Derekp978's avatar
    Derekp978
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      // (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