topic Rolling median not same as that in Excel in DAX Commands and Tips

Rolling median not same as that in Excel

sgupta22 — Thu, 22 Sep 2022 09:34:36 GMT

Hi,

I am trying to calculate the rolling median of a measure using the following formula:

Median EBIT margin = CALCULATE(MEDIANX('All Measures', [EBIT margin]), FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))

However, this formula gives me results which are different from those calculated in MS Excel using the same underlying raw data (from the 3rd row onwards). Below, I am giving the actual [EBIT margin] values along with the Median values that I get using Power BI and Excel. Would really appreciate if someone can point out what I am doing wrong or point me in the right direction.

Thank you very much!

Shashank

Date	Actual EBIT margin	Median using Excel	Median using Power BI
2010	5.7%	5.7%	5.7%
2011	7.2%	6.4%	6.4%
2012	11.7%	7.2%	8.2%
2013	8.6%	7.9%	8.3%
2014	13.7%	8.6%	9.5%
2015	9.7%	9.1%	9.6%
2016	10.8%	9.7%	9.8%
2017	15.7%	10.3%	10.8%
2018	14.5%	10.8%	11.3%

Ashish_Mathur

Re: Rolling median not same as that in Excel

johnt75 — Thu, 22 Sep 2022 10:47:51 GMT

You're not doing the iteration over the years, try

Median EBIT = CALCULATE ( MEDIANX ( VALUES ( 'Calendar'[Year] ), [EBIT] ), REMOVEFILTERS ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )

Re: Rolling median not same as that in Excel

sgupta22 — Thu, 22 Sep 2022 10:55:36 GMT

Thank you johnt75 for your reply.

Unfortunately, when I use the above formula, I don't get any values for the Median EBIT. It just becomes blank.

Re: Rolling median not same as that in Excel

johnt75 — Thu, 22 Sep 2022 11:05:22 GMT

Hmm, it worked for me.

Are you using the Calendar[Year] column in the table visual? What is the defintion of the [EBIT] measure ?

Re: Rolling median not same as that in Excel

sgupta22 — Thu, 22 Sep 2022 11:16:56 GMT

Yes, I am... Actually the measure is EBIT margin and it's formula is below:

EBIT margin = VAR Revenue = SUM(Data[Total Revenue]) VAR EBIT = [Adjusted EBIT] RETURN IF(Revenue = 0, BLANK(), EBIT / Revenue)

Re: Rolling median not same as that in Excel

Ashish_Mathur — Thu, 22 Sep 2022 11:24:43 GMT

Hi,

You may download my PBI file from here.

Hope this helps.

Re: Rolling median not same as that in Excel

sgupta22 — Thu, 22 Sep 2022 11:55:51 GMT

Thanks for your reply Ashish.

Strangely, it's not working in my file (getting blank) even though I can see you are getting the right values. There must be something wrong in my file. I am attaching it here if that helps.

Thank you very much for your help. Much appreicated.

Regards

Shashank

Re: Rolling median not same as that in Excel

Ashish_Mathur — Thu, 22 Sep 2022 12:23:28 GMT

Hi,

You may download my PBI file from here. Reapply the conditions in the slicers.

Hope this helps.

Re: Rolling median not same as that in Excel

sgupta22 — Thu, 22 Sep 2022 13:19:13 GMT

This works! Thanks a ton!! You are a genius.

Just one more question if I can indulge you. Is there a way the start date can be dynamic? For example, it should start with the minimum date at which the [EBIT margin] is not "blank" or "0"?

Thanks a ton again.

Cheers

Shashank

Re: Rolling median not same as that in Excel

Ashish_Mathur — Thu, 22 Sep 2022 13:51:09 GMT

You are welcome. Thank you for your kind words. Please download the revised file from here.

Hope this helps.

Re: Rolling median not same as that in Excel

sgupta22 — Thu, 22 Sep 2022 22:57:11 GMT

This is amazing 😊 Thank you again so much!

Re: Rolling median not same as that in Excel

Ashish_Mathur — Thu, 22 Sep 2022 22:59:20 GMT

You are welcome.