Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All
Got a brainteaser here.
I have a report that looks at a subset of products. From this I can get all the usual stuff (value, qtys etc) however what I want to find is the average order value of orders that contain my subset but not limited to .
Not a very good explanation so here is what Ive got so far
The total order value for order 123456 is correct because all it contains is Product X. However 258369 is wrong because its only looking at line 1, it should be £10 like below.
And because 147258 doesnt contain Product X its excluded. This makes the AOV £12.50
Hope you can help and I've explained it thoroughly enough
Cheers
Solved! Go to Solution.
Hi @MCornish ,
Hi @MCornish ,
You could create a column to set a flag.
Flag =
VAR a =
ADDCOLUMNS (
'Product',
"c", IF ( CONTAINSSTRING ( "X", 'Product'[Product] ), 0, 1 )
)
RETURN
PRODUCTX (
FILTER ( a, 'Product'[OrderNumber] = EARLIER ( 'Product'[OrderNumber] ) ),
[c]
)
Then create a measure to calculate average.
_AOV =
CALCULATE (
SUMX ( FILTER ( 'Product', 'Product'[Flag] = 0 ), 'Product'[Value] )
/ CALCULATE (
DISTINCTCOUNT ( 'Product'[OrderNumber] ),
FILTER ( 'Product', 'Product'[Flag] = 0 )
),
ALLSELECTED ( 'Product' )
)
Here is the result.
Hi @MCornish ,
Hi @MCornish ,
You could create a column to set a flag.
Flag =
VAR a =
ADDCOLUMNS (
'Product',
"c", IF ( CONTAINSSTRING ( "X", 'Product'[Product] ), 0, 1 )
)
RETURN
PRODUCTX (
FILTER ( a, 'Product'[OrderNumber] = EARLIER ( 'Product'[OrderNumber] ) ),
[c]
)
Then create a measure to calculate average.
_AOV =
CALCULATE (
SUMX ( FILTER ( 'Product', 'Product'[Flag] = 0 ), 'Product'[Value] )
/ CALCULATE (
DISTINCTCOUNT ( 'Product'[OrderNumber] ),
FILTER ( 'Product', 'Product'[Flag] = 0 )
),
ALLSELECTED ( 'Product' )
)
Here is the result.
Think I may have got it, can anybody validate that i'm not doing something weird
AOV =
VAR Orders = SUMMARIZECOLUMNS('Product Entries'[Order Number], "SalesValue", [Total Sales Value])
VAR AllOrders = INTERSECT( CALCULATETABLE( SUMMARIZECOLUMNS( 'Product Entries'[Order Number], "SalesValue", [Total Sales Value]), REMOVEFILTERS( 'Product Brand'[Brand Name] ) ), Orders )
RETURN
AVERAGEX( AllOrders, [SalesValue] )
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |