Forum Discussion
Calculating Median value by orders
I want to calculate the Medain for all my orders, but the orders are split into separate line by products. Please look into the table below.
| Order_ID | Quantity | Product number | Price of product |
| 0001 | 1 | 20001 | 11.05 |
| 0001 | 2 | 10001 | 10 |
| 0002 | 1 | 10001 | 10 |
| 0003 | 2 | 20001 | 11.05 |
| 0003 | 1 | 20002 | 12 |
| 0004 | 5 | 10002 | 5 |
| 0004 | 1 | 10001 | 10 |
| 0004 | 1 | 20002 | 12 |
I want to group all the orders and then calcualte meadian for the entire table. The median for the entire table should be {31.05, 10, 34.10, 47} = 32.575 ~ 32.58
I am using the following code to acheive this.
Median Order Value =
VAR IncomingSales = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Price of Product])
VAR SalesByOrder = SUMMARIZE('Sales', 'Sales'[Order_ID],"LineSales",[IncomingSales])
VAR MOV = MEDIANX('SalesByOrder',[LineSales]
RETURN MOV
Hi nikhil0511
You can use Dax code :Med = MEDIANX(SUMMARIZE('Table','Table'[Order_ID],"Order_cost",SUMX('Table',[Quantity]*[Price of product])),[Order_cost])pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi,
PBI file attached.
Hope this helps.
2 Replies
- Ritaf1983Super User
Hi nikhil0511
You can use Dax code :Med = MEDIANX(SUMMARIZE('Table','Table'[Order_ID],"Order_cost",SUMX('Table',[Quantity]*[Price of product])),[Order_cost])pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- Ashish_MathurSuper User