Search code examples
excelsumdaxpowerpivotmedian

Calculate median of aggregated values for a category using pivot table

I have hourly data about sales $ of certain categories (Fruits, snacks etc). I'd like to display the median of daily sales values over a month or an year using the hourly data in the table. So, for each category it needs to sum the hourly values and create a daily value first and then calculate the median

regular pivot does not provide median function. I am familiar with Power pivots but have not used Measures. i tried to create a measure using medianX function, but could not make it work

Solution

  • First, you will need to add a Calendar table to your data model:

    Calendar Table Explained

    The Calendar table must have fields you want to group your sales by, such as "Year-Month" and "Year-Quarter".

    Then, connect the Calendar table to your Sales table via date fields, to get a structure like this:

    enter image description here

    With this data model in place, create 2 measures ( I will assume your sales table is called "Sales", and calendar table is called "Date"):

    Measure 1: 

    Total Sale = SUM(Sales[Amount])

    It will simply calculate sum of sale amounts.

    Measure 2:

    Median Monthly Sale = MEDIANX( VALUES(Date[Year-Month]), [Total Sale])

    This measure will first sum up sales by Year-Month, and then calculate the median of the sums.

    To get median sales by a different period, just replace Year-Month with the desired grouping level, for example:

    Median Yearly Sale = MEDIANX( VALUES(Date[Year]), [Total Sale])