Slope and graph plot

[Courju]

Hi community !

I have a big database with a lot of data in it. Typically, I would have a "Table" with column "Result" and "Date". But, not all date would be populated.

I try to create a measure that would display the slope of the Results (to see the trend) of the selected time from filter (that will select based on Date).

If I plot Result against Date, there are some gap when no measurement was performed. As I am not interested in the numerical value of the slope, but on the ratio "slope/target" value to see if the trend is critically going up or down.

So, in fact, what I would need is a way to plot "Result" against X Axis 1-2-3-4-5-6- ... and get the slope from that.

Any Idea??

Thanks

Julien

[v-xiaotang]

Hi @Courju 

sorry for not very understand, 

based on the file I created, could you add more details about the expected result? 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Courju]

Here an example of the file:

https://1drv.ms/u/s!AmQCAddskXi4g9pIhiLCRwGzshJzsw?e=gnqffr

There, you might see that I had to fill up 

RegCoef-PropA and 

RegCoef-PropB

This is where the coefficient of the linear regression should go. And again, the problem is that the data should take care of the timeline but not of the gap between days. 

As it is important to see the trend of the data over time, but it does not matter if there were 1h or 10 days between two values !

I have the feeling that there is something to do with Rankx ... I spent hours on it, and can't figure it out.

For the calculation of the slope, I found that:

Simple linear regression =

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( Sheet2 ),

"Known[X]",Sheet2[Property] ,

"Known[Y]", Sheet2[Value]

),

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 Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X ^ 2

)

RETURN

Slope

... and it works ... but I still have the problem of having the "x" values. I think that this is the point that really hold me. I can index the rows, but then, when it get filtered, the index is then not continuous anymore.

[Courju]

Thanks for already starting to look at my request ... 😁

I wanted to make changes to your pbix, but somehow couldn't.

But, let me show it then, as you proposed:

Based on what you started:

I have a huge database from production samples. After slicer filtering down to one single property, I end up with a similar table than the one you proposed.

No average necessary, no sum, and but a numerical 1.2.3.4. is given automatically (of course, as it needs to follow the slicer choice, so it can't be just added to the database). If I do add an ID to each row, it would not be continuous, due to the lack of data on certain days, and for certain properties.

The plot is done of the numerical vs. day (no double point will be found as my values are refered with day and time and for one property, there is no double value then) and the linear regression done provides me the 0,3281. This is divided by the average of all data to rationalize the result. There I get 3,69% "increase".

Purpose is to obtain a simple visualisation like this:

Based on, let say 5%.

For a quality control lab, it will be a visualization for the manager to look at every 2-3 days. No graph, no values, only lights red or green, showing that the property is stable or not, but always based on the same rational increase.

Of course, a property measuring at average 0.62 is not impacted the same from a +1 unit than a property measuring at average 10500 ... that is clear.

Ok, I hope this is easier to understand now.

Thanks again for your help ... already hours trying that ...

Julien

[lbendlin]

@Courju You may want to try the built-in analytics options for your chart.  They include a trend line.