Get Median values for specific time range after aggregation

[Anonymous]

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.

[v-zhangti]

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]

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 · Accepted solution]

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.

[Anonymous]

Thank you very much @v-zhangti .

Your solution is very helpful for me.

Best Regards.