Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
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.?
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |