Forum Discussion
Get Median values for specific time range after aggregation
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.
Community SupportHi, 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.
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
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.