Forum Discussion

nikhil0511's avatar
nikhil0511
Advocate I
2 years ago
Solved

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_IDQuantityProduct numberPrice of product
000112000111.05
000121000110
000211000110
000322000111.05
000312000212
00045100025
000411000110
000412000212

 

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

     

2 Replies

  • 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