
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Analog of sum() over (partition by) SQL in measure
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?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Katerina,
Try this Expression:
=AGGR(NODISTINCT SUM(Quantity),[Chain name],Month,BU,Brand)
Regards,
Av

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Katerina,
Try this Expression:
=AGGR(NODISTINCT SUM(Quantity),[Chain name],Month,BU,Brand)
Regards,
Av

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, Av, thank you
