Forum Discussion
Dax help : aggregate values by specific columns
Hi,
I would like to generate "Aggregated_Qty" by adding "column," instead of "measure" in Power BI.
In this case, I would like to base on "Sales Order" and "SKU" to calculated aggregated qty.
For example, the first 1 and 2 lines are the same group of "Sales Order" and "SKU", so the "Aggregated_Qty" is 200+100=300.
Wonder if you could guid me how to write dax by adding column function? Thanks.
| Customer | Site | Sales Order | Line | SKU | Date | qty | Aggregated_Qty |
| A | BR | A125 | D1 | AA | 2022-05-10 | 200 | 300 |
| B | BR | B125 | D2 | AA | 2022-06-20 | 100 | 300 |
| A | CN | A123 | D2 | GG | 2022-06-15 | 100 | 200 |
| A | CN | A125 | D2 | GG | 2022-06-18 | 50 | 200 |
| B | CN | B125 | D1 | GG | 2022-06-19 | 50 | 200 |
| A | US | A123 | D1 | JJ | 2022-06-10 | 2000 | 2250 |
| B | US | B125 | D3 | JJ | 2022-06-21 | 200 | 2250 |
| B | US | B125 | D3 | JJ | 2022-07-21 | 50 | 2250 |
Anonymous create this column:
Agg Qty = VAR _site = 'Table'[Site] VAR _sku = 'Table'[SKU] RETURN CALCULATE( SUM('Table'[qty]), REMOVEFILTERS('Table'), 'Table'[Site] = _site, 'Table'[SKU] = _sku )
3 Replies
Anonymous create this column:
Agg Qty = VAR _site = 'Table'[Site] VAR _sku = 'Table'[SKU] RETURN CALCULATE( SUM('Table'[qty]), REMOVEFILTERS('Table'), 'Table'[Site] = _site, 'Table'[SKU] = _sku )SpartaBI Thanks. It is very helpful.
Anonymous my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂