Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cledevoute
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 NameYearTotal Rev ($USDmm)Total Revenue LYRev % Chanage YoY
1-800-FLOWERS.COM, Inc.2012$707.50  
1-800-FLOWERS.COM, Inc.2013$735.50707.54.00%
1-800-FLOWERS.COM, Inc.2014$756.30735.52.80%
1-800-FLOWERS.COM, Inc.2015$1,121.50756.348.30%
1-800-FLOWERS.COM, Inc.2016$1,173.001121.54.60%
1-800-FLOWERS.COM, Inc.2017$1,193.6011731.80%
1-800-FLOWERS.COM, Inc.2018 1193.6 
1st Source Corporation2012$227.00  
1st Source Corporation2013$233.302272.80%
1st Source Corporation2014$234.50233.30.50%
1st Source Corporation2015$247.70234.55.60%
1st Source Corporation2016$252.80247.72.10%
1st Source Corporation2017$275.40252.88.90%
1st Source Corporation2018 275.4 
2U, Inc.2012$55.90  
2U, Inc.2013$83.1055.948.70%
2U, Inc.2014$110.2083.132.60%
2U, Inc.2015$150.20110.236.30%
2U, Inc.2016$205.90150.237.10%
2U, Inc.2017$286.80205.939.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
v-yulgu-msft
Employee
Employee

Hi @cledevoute,

 

Please try this:

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.

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

 

YearTotal Rev ($USDmm)Total Revenue LYRev % Chanage YoYMED % Change YoY
2012$12,631,543.20   
2013$13,099,956.2012631543.253.70% 
2014$13,616,671.3013099956.243.90% 
2015$13,300,514.6013616671.28-2.30% 
2016$13,579,808.7013300514.562.10% 
2017$14,640,815.3013579808.737.80% 
2018$1,956,056.6014640815.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.?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.