Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LisaChung
Frequent Visitor

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.

 

CustomerSiteSales OrderLineSKUDateqtyAggregated_Qty
ABRA125D1AA2022-05-10200300
BBRB125D2AA2022-06-20100300
ACNA123D2GG2022-06-15100200
ACNA125D2GG2022-06-1850200
BCNB125D1GG2022-06-1950200
AUSA123D1JJ2022-06-1020002250
BUSB125D3JJ2022-06-212002250
BUSB125D3JJ2022-07-21502250
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
Community Champion

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

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

@SpartaBI Thanks. It is very helpful.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors