Dax help : aggregate values by specific columns

[LisaChung]

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

[SpartaBI · Accepted solution]

@LisaChung 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
)

Showcase Report – Contoso By SpartaBI

      

[LisaChung]

@SpartaBI Thanks. It is very helpful.

[SpartaBI]

@LisaChung 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 🙂