Sum OF values

[fab196]

Hi Everyone ,

need your help

i want use power query and generate the result below

I have with two columns first is original col and second one is derived column as mentioned the below

i want to amke a running toatl but in different way

in the example listed below if the values are not macthing from both columns a and b then the deafualt value is will 1 in the result col

if the values are macthing then it should be add 1 to each values

A	B			result
1	NULL		initial  value is nulll then it should be 1(in short values are not matching)	1
1	1		it is matching so added above value 1+ 1	2
2	1		both values are not macthing so	1
2	2		macthing then adding above 1+1	2
2	2		macthing so above 2+1	3
3	2		not macthing so 1	1
4	3		not macthing so 1	1
4	4		macthing so above 1 value and add 1 into that so 1+1	2

 @amitchandak 

@parry2k 

@v-luwang-msft 

[amitchandak · Accepted solution]

@fab196 , a new column

new column =

vat _b = [B]+0

return

countx(filter(Table, [A] = earlier([A]) && [B]+0 <= +b ), [A])

[v-yanjiang-msft · Accepted solution]

Hi @fab196 ,

According to your description, it's more easier to achieve it by DAX, here's my solution.

1.In Power Query, add an index column.

Get this table.

2.Create a calculated column.

Count =
VAR _RANK =
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ColumnA] = EARLIER ( 'Table'[ColumnA] )
                && 'Table'[ColumnB] = EARLIER ( 'Table'[ColumnB] )
                && 'Table'[ColumnA] = 'Table'[ColumnB]
        ),
        'Table'[Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( 'Table'[ColumnA] = 'Table'[ColumnB], _RANK + 1, _RANK )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak · Accepted solution]

@fab196 , a new column

new column =

vat _b = [B]+0

return

countx(filter(Table, [A] = earlier([A]) && [B]+0 <= +b ), [A])

[fab196]

can we do it using power query?

[amitchandak]

@fab196 , refer if this can help

https://www.youtube.com/watch?v=7CqXdSEN2k4

[fab196]

col a	PREVIOUS ROW
A	NULL	HERE IS NULL SO DEFAULT VALUE IS 1	1
A	A	A=A SO ADDITION LIKE ABOVE 1 VALUE PLUS ADD 1	2
A	A	A=A SO ADDITION LIKE ABOVE 1 VALUE PLUS ADD 2	3
B	A	B<>A SO	1
C	B	C<>B	1

I WANT TO RESULT LIKE THIS