M code and DAX running total

[dicken]

Hello,
pretty new to power query and dax, I've used < List.Sum list.firstN / Range and List.Accumulate to create running totals, and an index column in Dax power pivot
but I'd like to create one based on a condition, so at simples two columns 'Product ' a b c and 'sales' and create a running total of sales based on the product ;
so in Excel < sumif ( product range, 1st cell of product range (ie ($A$1:A1),range to sum >

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    RunningTotal = Table.AddColumn(AddIndex, "Running Total", each List.Sum(Table.SelectRows(Table.FirstN(Source,[Index]), (x)=> x[Product]=_[Product])[Sales])),
    Result = Table.RemoveColumns(RunningTotal,{"Index"})
in
    Result

Book1
	A	B	C	D	E	F	G
1	Product	Sales		Product	Sales	Running Total
2	A	15		A	15	15
3	B	11		B	11	11
4	A	12		A	12	27
5	B	6		B	6	17
6	B	18		B	18	35
7	B	13		B	13	48
8	C	1		C	1	1
9	A	20		A	20	47
10	C	14		C	14	15
11	B	9		B	9	57
12	A	21		A	21	68
13	A	5		A	5	73
14	C	8		C	8	23
15	A	7		A	7	80
16	A	3		A	3	83
17	B	17		B	17	74
18	A	10		A	10	93
19	C	16		C	16	39
20	C	19		C	19	58
21	B	4		B	4	78
22	A	2		A	2	95
23
Sheet1

 

[dicken]

Hi JGordon,
not had a chance to try it but reading through it look very promising, I'm in middle of something else but will try tomorrow and let you know.

Rd

 

[dicken]

Hi,
Gordon,
Just tried it out and works fine, now the hard part , understanding all the syntax, any help espeically regarding the use of the variable, which I'm pretty shaky on, would be gratefully received.

Richard.

 

[JGordon11]

Both Table.AddColumn and Table.SelectRows have a function parameter that take the current row (as record) of their first parameter table and assigns it to its function variable.

a function is defined by wrapping a variable in parentheses followed by the "gets" operator =>

so (x)=> 2*x is a simple doubling function using the variable x, this would throw an error if used in Table.SelectColumns because a record multiplied by two doesn't compute.

But (x)=> (x[Product] = "A") would not throw an error because if would return true if the product in the current row is "A" and false otherwise.

a shorthand for function definitions is the each keyword that always uses the underscore as its variable name, so

each 2*_ is the same as (_)=> 2*_

Since I am using nested functions that have a function parameter I can't use each keyword in both because the single variable _ will only apply to one of the functions.

So I used (x)=> in one so I could refer its row reference x and compare it to the prior function's row reference _

 

[dicken]

thanks, especially re (_) which I wasn't sure about, still lots to leaarn.

RD

 

[JGordon11]

This way of writing the RunningTotal step may be more self explanatory:

RunningTotal = Table.AddColumn(AddIndex, "Running Total", (TableAddColumnRow)=>
List.Sum(Table.SelectRows(Table.FirstN(Source,[Index]), (x)=> x[Product]=_[Product])[Sales])),

 

[JGordon11]

Disregard previous transmission.

 

[dicken]

This way of writing the RunningTotal step may be more self explanatory:

RunningTotal = Table.AddColumn(AddIndex, "Running Total", (TableAddColumnRow)=>
List.Sum(Table.SelectRows(Table.FirstN(Source,[Index]), (x)=> x[Product]=_[Product])[Sales])),

Thanks once again, I think I shall be investing in DAX book before too long.
Richard.

 

[dicken]

This way of writing the RunningTotal step may be more self explanatory:

RunningTotal = Table.AddColumn(AddIndex, "Running Total", (TableAddColumnRow)=>
List.Sum(Table.SelectRows(Table.FirstN(Source,[Index]), (x)=> x[Product]=_[Product])[Sales])),

Could you elaborate on the step < (x)=> x[Criteria] = _ [Criteria])[Sales]) > if x represents criteria , what is x[Criteria] doing ? Can I take it that _ indicates all rows of the table. Any recommendations re things i should read would be welcome.

I have to say the closest I've every got to coding is a visual basic course over 30 years ago.

Richard