Displaying Trending (Slope) data from a PowerQuery

[lordneeko]

Greetings,

I've been banging my head on this for quite some time now. I've tried many different paths and none are getting me where I want to be.

I have a huge set of data (pulled from SharePoint) which I'm doing a lot of manipulation to within PowerQuery and spitting out a table.
That data is being plot to a chart and I can create a trend line there exactly like I want it.

However, I have another chart that is essentially an "Executive Dashboard" overview of the "lower level" charts which displays essential information on each column, with each column's data coming from the data of other charts.

Example Tables which get generated from multiple PowerQueries

Phase A
Title Time_to_process_days ProcessType INDEX
System A 100 ProcessA 1
System B 35 ProcessB 2
System C 85 ProcessA 3


Phase B
Title Time_to_process_days ProcessType INDEX
System A 15 ProcessA 1
System B 65 ProcessB 2
System C 50 ProcessA 3



Executive Chart
Combo chart with each series being a different marker
Series for MAX, MIN, Target, Average, StandardDeviation



My initial idea was to use the subtotal row on each table to calculate slope
So I added an Index column to my PowerQuery which added the Index column you see above.
=Slope(Time_to_process_days, INDEX)

This works, except I want the formula to respect the filtering of "ProcessType" but the slope function doesn't do that inherently like the subtotal or aggregate functions can do.

So, I added a column (outside powerQuery because PQ couldn't do it) to each table called isVisible and used the formula
=Subtotal(3, [Title])

and then I changed my slope function to (and used the CTRL+SHIFT+ENTER to enter)
{=SLOPE(Phase A[Time_to_process_days],IF(Phase A[isVisible],Phase A[Index],""))}

And this APPEARS to work. However, when I filter the ProcessType for (say) Process A I get this
Phase A
Title Time_to_process_days ProcessType INDEX
System A 100 ProcessA 1
System C 85 ProcessA 3


Phase B
Title Time_to_process_days ProcessType INDEX
System A 15 ProcessA 1
System C 50 ProcessA 3


So what happens is, it screws up the SLOPE calculation because the X-value goes from {1,3}, instead of {1,2}


Basically, I need to be able to calculate SLOPE of data that DOESN'T have an X value, and have it assume the x is 1->count. I tried passing the ROW(1:count) function to the x-values, but it can't do that.




The end goal is to then take the SLOPE that is calculated (based on the filtered data set) and plop that as a graph element on the Executive Graph. Something like an "Up Arrow" or "Down Arrow". And this is a whole new adventure to figure out. Right now I just need to get my slope calculations done correctly.


Enough details?

[lordneeko]

ok I'm stupid, the answer is to use LINEST instead of SLOPE
I still have question about how to use LINEST with filtered data though...so that is only calculates VISIBLE rows.

[lordneeko]

I now have this (with a helper column)

{=LINEST(IF([Helper],[Time_to_process_days]))} And this works! .... except when I filter the data.
Technically it IS working when I filter the data, but the If statement ends up feeding this array into the LINEST function

{FALSE;FALSE;FALSE;1;FALSE;FALSE;3;FALSE}

Instead of truncating to just {1;3}

How can I easily truncate an array within the formula?
I tried using LARGE(), but that gave me #NA for the False values.

[CK76]

Personally, I prefer to filter data set before returning it to worksheet from PQ. Using named range(s) to pass parameter to PQ.

However, if you want to keep it as is. You can adapt XOR LX's article.
https://excelxor.com/2016/02/16/crit...-logest-trend/

Add helper column indicating if row is visible or not (using SUBTOTAL). Assuming there's not blanks.
Ex: =SUBTOTAL(2,Cell) if cell holds numeric value. =SUBTOTAL(3,Cell) if cell can have text.

Then use this column for condition check in XOR LX's formula construct.

[lordneeko]

That would make sense, but I HAVE to have the unfiltered data, because then I'm using a Slicer and parameters because I then hook into those from a SharePoint site

Yes....it really is that large and complicated lol
Thanks I'll look at this.

[CK76]

If you use Data Model (PowerPivot, PowerView) in conjunction with PowerQuery. You can use DAX to perform linear regression.
For an example... adapted from Daniil Maslyuk's article. Using CALCULATETABLE (VALUES(),ALLSELECTED()) instead of ALLSELECTED().
Assuming that Table[Column] holds sequential numeric values (i.e. known _X).

Simple linear regression = 
VAR Known = 
    FILTER ( 
        SELECTCOLUMNS ( 
            CALCULATETABLE ( VALUES ( Table[Column] ), ALLSELECTED ( Table ) ), 
            "Known[X]", Table[Column], 
            "Known[Y]", [Measure Y] 
        ), 
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) ) 
    ) 
VAR Count_Items = 
    COUNTROWS ( Known ) 
VAR Sum_X = 
    SUMX ( Known, Known[X] ) 
VAR Sum_X2 = 
    SUMX ( Known, Known[X] ^ 2 ) 
VAR Sum_Y = 
    SUMX ( Known, Known[Y] ) 
VAR Sum_XY = 
    SUMX ( Known, Known[X] * Known[Y] ) 
VAR Average_X = 
    AVERAGEX ( Known, Known[X] ) 
VAR Average_Y = 
    AVERAGEX ( Known, Known[Y] ) 
VAR Slope = 
    DIVIDE ( 
        Count_Items * Sum_XY - Sum_X * Sum_Y, 
        Count_Items * Sum_X2 - Sum_X ^ 2 
    ) 
VAR Intercept = Average_Y - Slope * Average_X 
RETURN 
    SUMX ( DISTINCT ( Table[Column] ), Intercept + Slope * Table[Column] )  

See article below.
https://xxlbi.com/blog/simple-linear-regression-in-dax/

[lordneeko]

Can't do that with our spreadsheet
Nothing that works like
({FALSE;FALSE;FALSE;1;FALSE;FALSE;3;FALSE}).RemoveAll(item => item == FALSE); ???

[CK76]

Then just use the SUBTOTAL() helper column with XOR LX's formula construct.

[lordneeko]

Ok, well certainly the attached site HELPED...but I wasn't able to apply exactly how he did it...but that's ok, here is what I did

{=LINEST(N(IF(1,MODE.MULT(IF([isVisible],{1,1}*[Time_to_process_days])))))} (With CTRL+SHIFT+ENTER)

This works perfectly with the filtered data!!!

[CK76]

Thanks sharing the solution and for the rep.