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.
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] )
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 |
---|---|
111 | |
109 | |
89 | |
76 | |
67 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |