cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## New to PowerBI: How to create trendline for non-conformances

Good Morning All,

I'm new to PowerBI and Excel as a logical tool. I've always used this Microsoft product as a statistical tool.

My first task at my new job is to create Quality KPI metrics. I'm stuck on creating a trendline for:

1)overdue (30+days) non conformance that are open. My current powerBI build is using a splicer that slides to get the answer (example Jan2019-May2021, Jan2019-April2021, etc). What logical test can I apply that the formula is more dynamic?

2) Non-conformance per month?

Any help from the community is greatly appricated.

1 ACCEPTED SOLUTION
Community Support

Hi @edidonato ,

Not certian what is your data source look like and the expected result but basically the calculation logic of the trendline would be like this:

``````Linear regression =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Table'[Date] ),
"Known[X]", 'Table'[Date],
"Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
),
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] * Known[X] )
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 * Sum_X
)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )``````

You can also refer this simliar issue: Trend line values

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @edidonato ,

Not certian what is your data source look like and the expected result but basically the calculation logic of the trendline would be like this:

``````Linear regression =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Table'[Date] ),
"Known[X]", 'Table'[Date],
"Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
),
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] * Known[X] )
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 * Sum_X
)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )``````

You can also refer this simliar issue: Trend line values

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.