Forum Discussion

KJNelson's avatar
KJNelson
Frequent Visitor
2 years ago
Solved

Medianx formula help

I have a table with a row for every order and the date the order was placed. I am trying to find the median number of orders placed per day and per month. Can anyone help me with the correct formula?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi KJNelson ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the new table. Create the new measure to calculate median of orders per day.

    DailyOrders = 
SUMMARIZE(
    'Table',
    [Order Date],
    "DailyOrderCount", COUNT('Table'[Order ID])
)

    MedianOrdersPerDay = 
MEDIANX(
    DailyOrders,
    DailyOrders[DailyOrderCount]
)

    3.Create the new column.

    Month = FORMAT([Order Date], "YYYY-MM")

    4.Create the new table and create the measure to calculate median of orders per month.

    OrdersPerMonth = 
SUMMARIZE(
    'Table',
    [Month],
    "MonthlyOrderCount", COUNT('Table'[Order ID])
)
    MedianOrdersPerMonth = MEDIANX(OrdersPerMonth, [MonthlyOrderCount])

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    KJNelson , Based on what i got try like

     

    MEDIANX(Values(Date[Date]), [Measure])

     

    MEDIANX(Summarize(Table,Date[Month], Date[Day]), [Measure])

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi KJNelson ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the new table. Create the new measure to calculate median of orders per day.

    DailyOrders = 
SUMMARIZE(
    'Table',
    [Order Date],
    "DailyOrderCount", COUNT('Table'[Order ID])
)

    MedianOrdersPerDay = 
MEDIANX(
    DailyOrders,
    DailyOrders[DailyOrderCount]
)

    3.Create the new column.

    Month = FORMAT([Order Date], "YYYY-MM")

    4.Create the new table and create the measure to calculate median of orders per month.

    OrdersPerMonth = 
SUMMARIZE(
    'Table',
    [Month],
    "MonthlyOrderCount", COUNT('Table'[Order ID])
)
    MedianOrdersPerMonth = MEDIANX(OrdersPerMonth, [MonthlyOrderCount])

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.