I am trying make projections using the data I have for future years using the LINEST_M and LINEST_B functions. The problem is that they aren't working with the Fractile() function. I read that aggregation functions need to use the TOTAL qualifier, but when I use it it doesn't give me the correct answer. Is there a way around this issue?
Expression is close to y=m*x+b where b is =LINEST_B(Fractile(TOTAL [My Value],0.9),[Year]) and m is LINEST_M(Fractile(TOTAL [My Value],0.9),[Year])
When I check the linear trendline equation on a line chart b should be close to -3.9 and m should be close to 0.002
I found a solution. If someone else has this problem you can model your expression after this one. This will give you the same result as the trendline equation in a chart for a 90th percentile trend.
Slope =(count(DISTINCT [Year])*Sum(DISTINCT TOTAL [Year]*Aggr(Fractile([My Value],0.9),[Year]))-(Sum(DISTINCT TOTAL [Year])*Sum(DISTINCT TOTAL Aggr(Fractile([My Value],0.9),[Year]))))/(count(DISTINCT [Year])*Sum(DISTINCT TOTAL Pow([Year],2))-Pow(Sum(DISTINCT TOTAL [Year]),2))
Y-Intercept =Avg(DISTINCT TOTAL Aggr(Fractile([My Value],0.9),[Year]))-vSlope*Avg(DISTINCT TOTAL [Year])
I found a solution. If someone else has this problem you can model your expression after this one. This will give you the same result as the trendline equation in a chart for a 90th percentile trend.
Slope =(count(DISTINCT [Year])*Sum(DISTINCT TOTAL [Year]*Aggr(Fractile([My Value],0.9),[Year]))-(Sum(DISTINCT TOTAL [Year])*Sum(DISTINCT TOTAL Aggr(Fractile([My Value],0.9),[Year]))))/(count(DISTINCT [Year])*Sum(DISTINCT TOTAL Pow([Year],2))-Pow(Sum(DISTINCT TOTAL [Year]),2))
Y-Intercept =Avg(DISTINCT TOTAL Aggr(Fractile([My Value],0.9),[Year]))-vSlope*Avg(DISTINCT TOTAL [Year])
