Forum Discussion

PowerBIUserX's avatar
PowerBIUserX
Frequent Visitor
2 years ago
Solved

Trend analysis using linear regression gradient per category

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 regressio...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.