Analog of sum() over (partition by) SQL in measure

[Katerina13]

Hi all,

I cannot generate a formula with a sense of sum(Quantity) over (partition by Chain_name,month, BU, brand) in pivot table.

Tried to SUM(AGGR(Quantity,[Chain Name],Month,BU,Brand)) - it works only for data with final step of Brand, but my next column is address_id, it is obviously shows 0,but I would like to get the total by [Chain Name],Month,BU,Brand.

Ex^

Chain name	Month	BU	Brand	address id	Quantity	sum(Quantity) over (partition by Chain_name,month, BU, brand
Masha	1	Nachos	X	12	1	3
Masha	1	Nachos	X	13	2	3
Masha	1	Chips	XXX	13	22	22
Bear	1	Nachos	XX	666	4	9
Bear	1	Nachos	XX	13	5	9
Bear	1	Chips	V	13	6	6
Dasha	1	Nachos	X	4	7	4
Dasha	1	Nachos	XX	666	8	17
Dasha	1	Chips	XX	1	9	17

Any ideas?

[aveeeeeee7en]

Hi Katerina,

I think the calculation you've shared for Dasha Chain Name is incorrect.

Please see the below image and confirm which value is correct.

Regards,

Av

[aveeeeeee7en · Accepted solution]

Hi Katerina,

Try this Expression:

=AGGR(NODISTINCT SUM(Quantity),[Chain name],Month,BU,Brand)

Regards,

Av

[Katerina13]

Hi, Av, thank you