cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Median of a measure dependent on other measures and date table

Hi -- I'm having trouble taking the median value of a population of companies' revenue growth % for a number of years.

Below is a sample of the table I'm working off of (Company Name  is from 1 table, Year is from a Date/Calendar table, and the next 3 are Measures based on the Company Name table):

 Company Name Year Total Rev (\$USDmm) Total Revenue LY Rev % Chanage YoY 1-800-FLOWERS.COM, Inc. 2012 \$707.50 1-800-FLOWERS.COM, Inc. 2013 \$735.50 707.5 4.00% 1-800-FLOWERS.COM, Inc. 2014 \$756.30 735.5 2.80% 1-800-FLOWERS.COM, Inc. 2015 \$1,121.50 756.3 48.30% 1-800-FLOWERS.COM, Inc. 2016 \$1,173.00 1121.5 4.60% 1-800-FLOWERS.COM, Inc. 2017 \$1,193.60 1173 1.80% 1-800-FLOWERS.COM, Inc. 2018 1193.6 1st Source Corporation 2012 \$227.00 1st Source Corporation 2013 \$233.30 227 2.80% 1st Source Corporation 2014 \$234.50 233.3 0.50% 1st Source Corporation 2015 \$247.70 234.5 5.60% 1st Source Corporation 2016 \$252.80 247.7 2.10% 1st Source Corporation 2017 \$275.40 252.8 8.90% 1st Source Corporation 2018 275.4 2U, Inc. 2012 \$55.90 2U, Inc. 2013 \$83.10 55.9 48.70% 2U, Inc. 2014 \$110.20 83.1 32.60% 2U, Inc. 2015 \$150.20 110.2 36.30% 2U, Inc. 2016 \$205.90 150.2 37.10% 2U, Inc. 2017 \$286.80 205.9 39.30% 2U, Inc. 2018 286.8

Rev % Chanage YoY = DIVIDE([Diff. Between Rev TY and LY], [Total Revenue LY])

I've tried to use MEDIANX():

MED % Change YoY = MEDIANX(FILTER(Normalized_Test, NOT(ISBLANK([Rev % Chanage YoY]))), [Rev % Chanage YoY])

I keep getting (Blank) value returns, even after trying to control for that. Clearly there's something I'm missing, or I'm not approaching the question correctly.

I'm trying to get the median value of all companies per year that will work with other filters on the table.

Any insight would be greatly appreciated, thanks.

2 REPLIES 2
Community Support Team

## Re: Median of a measure dependent on other measures and date table

Hi @cledevoute,

```MED % Change YoY =
CALCULATE (
MEDIANX (
FILTER ( Normalized_Test, NOT ( ISBLANK ( [Rev % Chanage YoY] ) ) ),
[Rev % Chanage YoY]
),
ALL ( Normalized_Test[Company Name] )
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Median of a measure dependent on other measures and date table

Hmmm, thanks v-yulgu-msft -- still throwing up blanks.  Table with the attempted formula use below (All companies):

 Year Total Rev (\$USDmm) Total Revenue LY Rev % Chanage YoY MED % Change YoY 2012 \$12,631,543.20 2013 \$13,099,956.20 12631543.25 3.70% 2014 \$13,616,671.30 13099956.24 3.90% 2015 \$13,300,514.60 13616671.28 -2.30% 2016 \$13,579,808.70 13300514.56 2.10% 2017 \$14,640,815.30 13579808.73 7.80% 2018 \$1,956,056.60 14640815.31 -86.60%

Could this be an issue with using a calendar table (seperate from the table that houses the Company data).

More background:

For tables (3 involved) I have Calendar[Date] = Normalized_Test[Fiscal Year]; Normalized_Test[SIC Code] = ref_SIC_Codes[SIC Code]

Normalized_Test is where I'm running the measures to get the Year-over-Year data.

Total Rev (\$USDmm) = SUM([Total Revenue (\$USDmm)])

Total Revenue (\$USDmm) LY = CALCULATE([Total Rev (\$USDmm)], SAMEPERIODLASTYEAR('Calendar'[Date]))

Diff. Between Rev TY and LY = IF(OR(ISBLANK([Total Revenue LY]),ISBLANK([Total Rev (\$USDmm)])), BLANK(), [Total Rev (\$USDmm)] - [Total Revenue LY])

And then, use these to calculate YoY: Rev % Chanage YoY = DIVIDE([Diff. Between Rev TY and LY], [Total Revenue LY])

And then from here is where the problem happens -- trying to create a Median measure on top of the Rev % Change YoY

Trying to think through the problem outloud -- I need to have DAX iterate over every instance of a company's YoY by each year. So, the measure would take every single instance of 2013's Rev % Change YoY for each company (~2700 rows), and return the median value.

Since I'm using a separate Calendar table for date calculations, would I need to include that somehow in the calculation RELATED(), etc.?