The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a fact table like the one below. I need to create a measure calculating the average value of order (OrderKey)
OrderNumber | CustomerCountry | OrderType | ProductNumber | SalesQuantity | SalesRevenue | OrderKey |
1 | US | XX | xc1353 | 56 | 42,540 | 1USXX |
1 | US | XX | vf4535 | 4 | 2,576 | 1USXX |
1 | US | XX | hg3563 | 44 | 45,562 | 1USXX |
1 | US | XX | kj7898 | 12 | 12,354 | 1USXX |
2 | UK | KY | vf4535 | 12 | 7,728 | 2UKKY |
2 | UK | KY | hg3563 | 123 | 127,367 | 2UKKY |
2 | UK | KY | kj7898 | 35 | 36,033 | 2UKKY |
Would you help?
Solved! Go to Solution.
try this:
Avg Order Revenue =
AVERAGEX (
DISTINCT ( 'Table'[OrderKey] ),
CALCULATE ( SUM ( 'Table'[SalesRevenue] ) )
)
Hi @zirael ,
Please have a try.
Create a measure.
ave_measure =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[OrderNumber] = SELECTEDVALUE ( 'Table'[OrderNumber] )
),
'Table'[SalesRevenue]
)
Or a column.
ave =
AVERAGEX (
FILTER ( 'Table', 'Table'[OrderNumber] = EARLIER ( 'Table'[OrderNumber] ) ),
'Table'[SalesRevenue]
)
If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @zirael ,
Please have a try.
Create a measure.
ave_measure =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[OrderNumber] = SELECTEDVALUE ( 'Table'[OrderNumber] )
),
'Table'[SalesRevenue]
)
Or a column.
ave =
AVERAGEX (
FILTER ( 'Table', 'Table'[OrderNumber] = EARLIER ( 'Table'[OrderNumber] ) ),
'Table'[SalesRevenue]
)
If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
try this:
Avg Order Revenue =
AVERAGEX (
DISTINCT ( 'Table'[OrderKey] ),
CALCULATE ( SUM ( 'Table'[SalesRevenue] ) )
)
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
8 | |
8 |