The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a visual that looks like this
Customer ID | average order value | median (should be) |
01 | 67 | 64 |
02 | 17 | 64 |
03 | 23 | 64 |
04 | 55 | 64 |
05 | 90 | 64 |
average order value is a measure
average order value = sum(order[sales])/distinctcount(order[order ID])
How can I calculate the median of the average median value.
I tried the following measure but it returned a different median (52)
median = MEDIANX(order, CALCULATE(SUM(order[sale])/DISTINCTCOUNT(order[order ID]),ALL(order)))
Thanks in advance!
Solved! Go to Solution.
@Anonymous
Give this a try.
median = CALCULATE ( MEDIANX ( ALL ( 'order'[order ID] ), [average order value] ), REMOVEFILTERS ( 'order'[Customer] ) )
Multiple lines on the same order may be causing the problem. Give this a try.
median = MEDIANX ( ALL ( 'order'[order ID] ), [average order value] )
Hi ,thanks for your solution, it got me very close.
But I got the following result.
Customer ID | average order value | median |
01 | 67 | |
02 | 17 | |
03 | 23 | |
04 | 55 | |
05 | 90 | |
total | 61.5 | 62 |
Any idea how it can return the same number in each row instead just in the total. Because I am going to need it for a following calculation.
@Anonymous
Give this a try.
median = CALCULATE ( MEDIANX ( ALL ( 'order'[order ID] ), [average order value] ), REMOVEFILTERS ( 'order'[Customer] ) )
@Anonymous , Try like
calculate( MEDIAN(order[sales]) , ALL(order))
or
calculate( Average(order[sales]) , ALL(order))
Hi thank you for the response.
I think your solution gives the median value of all of the orders.
I'm trying to get the median value of the average order value of all customers. Could you elaborate based on your solution please?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
94 | |
86 | |
69 | |
65 |
User | Count |
---|---|
232 | |
128 | |
116 | |
82 | |
82 |