How to calculate percentage change by groups?

[roalisco]

Hi everyone, 
Firs time posting a quetion here....
I have the following dataset where my Index column resets for each check_id:

check_id	ok_rows	Index
1112	1	1
1112	8	2
1112	4	3
1113	100	1
1113	100	2
1113	50	3
1113	100	4

I want to calculate (in DAX or Power Query) the percent change of every check_id from its previous (i.e. Index) value, so that I can have the following output:

check_id	ok_rows	Index	%_change
1112	8	1
1112	16	2	100%
1112	4	3	-75%
1113	100	1
1113	100	2	0%
1113	50	3	-50%
1113	100	4	100%

I appreciate any help with this. 
Many thanks in advance,
 

[ThxAlot]

%_change = 
VAR __prv =
    SELECTCOLUMNS(
        OFFSET(
            -1,
            ALLSELECTED( DATA[check_id], DATA[Index], DATA[ok_rows] ),
            ORDERBY( DATA[Index] ),
            PARTITIONBY( DATA[check_id] )
        ),
        DATA[ok_rows]
    )
RETURN
    IF( NOT ISBLANK( __prv ), DATA[ok_rows] / __prv - 1 )

[rajendraongole1 · Accepted solution]

Hi @roalisco - you can check below @bhanu_gautam suggested via Power query 

adding to the sme i have checked via calculated column in dax, 

%_change =

VAR CurrentValue = 'rowch'[ok_rows]

VAR PreviousValue =

    CALCULATE(

        MAX('rowch'[ok_rows]),

        FILTER(

            'rowch',

            'rowch'[check_id] = EARLIER('rowch'[check_id]) &&

            'rowch'[Index] = EARLIER('rowch'[Index]) - 1

        )

    )

RETURN

IF(

    ISBLANK(PreviousValue),

    BLANK(),

    DIVIDE(CurrentValue - PreviousValue, PreviousValue, 0)

)

you can try 

Hope it helps.

[roalisco]

Many thanks!

[bhanu_gautam]

@roalisco , Try below mentioned m-code for Power Query

let
Source = YourDataSource,
SortedRows = Table.Sort(Source,{{"check_id", Order.Ascending}, {"Index", Order.Ascending}}),
AddedIndex = Table.AddIndexColumn(SortedRows, "Index1", 0, 1, Int64.Type),
AddedPreviousValue = Table.AddColumn(AddedIndex, "PreviousValue", each try if [Index1] = 0 then null else AddedIndex{[Index1]-1}[ok_rows] otherwise null),
AddedPercentChange = Table.AddColumn(AddedPreviousValue, "%_change", each if [PreviousValue] = null then null else ([ok_rows] - [PreviousValue]) / [PreviousValue]),
RemovedColumns = Table.RemoveColumns(AddedPercentChange,{"Index1", "PreviousValue"})
in
RemovedColumns