Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Get Median values for specific time range after aggregation

Hello everyone,

I am struggling to get a MEDIAN value for a specific time limit (just only for the first 3 or 4 months).

The data set is daily data but I would like to compute the Median value after aggregating by monthly.

Date Users
9/7/2022 29
9/12/2022 21
9/16/2022 20
9/30/2022 28
10/1/2022 24
10/5/2022 15
10/18/2022 13
10/26/2022 30
10/31/2022 24
11/10/2022 15
11/15/2022 24
11/17/2022 12
11/23/2022 23
11/30/2022 27
12/2/2022 26
12/6/2022 20
12/13/2022 14
12/14/2022 17

I have the above table and it would be easy if we get the median value from this dataset.


But what I am trying is that SUM( [Views] ) by monthly and finding MEDIAN, it would be for 3 months or 4 months.

So, monthly views, Median and Median for the first 3 month would be look like below,

 Year Month Total Views Median Median for 3 months

2022

 9 98 99.5 101
2022 10 106 99.5 101
2022 11 101 99.5 101
2022 12 77 99.5 101

 

The presenting would be just like above table or creating a line chart over Year,Month level.

Please help me to write the DAX formula to get this MEDIAN value.

Thank you for your help and if the question does not make sense, I will try to rephrase.

Thanks.

Need Help
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    4month = 
Var _table=SUMMARIZE('Table','Table'[Year],'Table'[Month],"Sum",[Total View])
Return
MEDIANX(_table,[Sum])

    3month = 
Var _table=SUMMARIZE('Table','Table'[Year],'Table'[Month],"Sum",[Total View])
Return
MEDIANX(FILTER(_table,[Month]>=9&&[Month]<=11),[Sum])

    2month = 
Var _table=SUMMARIZE('Table','Table'[Year],'Table'[Month],"Sum",[Total View])
Return
MEDIANX(FILTER(_table,[Month]>=9&&[Month]<=10),[Sum])

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

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

4 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.
    Measure:

    Total Views = CALCULATE(SUM('Table'[Users]),ALLEXCEPT('Table','Table'[Year],'Table'[Month]))
    MEDIAN = MEDIANX(ALL('Table'),[Total Views])

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you v-zhangti . Your answer helps me a lot.

       

      I am curious why the MEDIAN did not result 99.5?

      MEDIAN should be 99.5 and MEDIAN for the first 3 months should be 101.00. Am I right?

       

      May I know how can I customize if I want to calculate only just for 2 months, 3 months, etc?

      • v-zhangti's avatar
        v-zhangti
        Community Support
        3 years ago

        Hi, Anonymous 

         

        You can try the following methods.

        Measure:

        4month = 
Var _table=SUMMARIZE('Table','Table'[Year],'Table'[Month],"Sum",[Total View])
Return
MEDIANX(_table,[Sum])

        3month = 
Var _table=SUMMARIZE('Table','Table'[Year],'Table'[Month],"Sum",[Total View])
Return
MEDIANX(FILTER(_table,[Month]>=9&&[Month]<=11),[Sum])

        2month = 
Var _table=SUMMARIZE('Table','Table'[Year],'Table'[Month],"Sum",[Total View])
Return
MEDIANX(FILTER(_table,[Month]>=9&&[Month]<=10),[Sum])

        Is this the result you expect?

        Best Regards,

        Community Support Team _Charlotte

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