Trend analysis using linear regression gradient per category

[PowerBIUserX]

Hi,

I'm building a report and need to calculate a trend over time per category, to identify top increases/decreases over time.

The end goal is to have a trend value (gradient of linear regression) per category, to then filter on the top 10 by trend value.

I've seen the following article on linear regression, but don't know how to go about applying it per category. https://community.fabric.microsoft.com/t5/Desktop/Trend-line-values/td-p/1641410

Additionally (nice to have), I would like to exclude any potential errors, for example by removing the top5%/bottom 5% of data (by value per category).

I appreciate any help!

Few comments:

• My Value from below is actually the result of a measure
• There are between 100-200 categories.
• The trend value in itself is rather meaningless and just used to sort/filter on categories.

Essentially, I would like to get from:

To this:

[PowerBIUserX]

Hi @v-xinruzhu-msft ,

Thank you for your reply. While it works on the limited dataset I initially provided, I do not think the solution works in all cases.
If I understand correctly, the measures you suggested calculate the slope [ (y2-y1)/(x2-x1) ] of the maximum point and minimum point in the dataset, but not the slope of the regression. In the dataset it works because they are already linear functions.

On the following dataset (non-linear function), the slope I am expected is -3.5%, but your measure returns -45%.

Do you have an idea on how to achieve the same but with the slope of the linear regression?

[v-xinruzhu-msft · Accepted solution]

Hi @PowerBIUserX 

I add a month column in the table

Then modifiy the Lines measure

Lines =
VAR a =
    LINESTX (
        FILTER ( ALLSELECTED ( 'Table' ), [Category] IN VALUES ( 'Table'[Category] ) ),
        [Value_measure],
        [Month]
    )
RETURN
    MINX ( a, [Slope1] )

Output

Best Regards!

Yolo Zhu

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

[PowerBIUserX]

Works perfectly! Thank you so much 😊

[v-xinruzhu-msft]

Hi @PowerBIUserX 

You can refer to the following solution.

Sample data 

I create a sample measure

Value_measure = SUM('Table'[Value]) 

Then create a measure

Lines =
VAR _minxy =
    MINX (
        FILTER ( ALLSELECTED ( 'Table' ), [Category] IN VALUES ( 'Table'[Category] ) ),
        [Value_measure]
    )
VAR _maxy =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [Category] IN VALUES ( 'Table'[Category] ) ),
        [Value_measure]
    )
VAR x_minxy =
    MINX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Category]
                IN VALUES ( 'Table'[Category] )
                    && [Value_measure] = _minxy
        ),
        MONTH ( [Date] )
    )
VAR x_maxy =
    MINX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Category]
                IN VALUES ( 'Table'[Category] )
                    && [Value_measure] = _maxy
        ),
        MONTH ( [Date] )
    )
RETURN
    DIVIDE ( _maxy - _minxy, x_maxy - x_minxy )

Best Regards!

Yolo Zhu

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