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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Raymz112
Helper I
Helper I

DAX Group by Calculation

Hello,

 

Please can someone help me with my calculation.

 

I have a dataset which looks like the below, I cannot change the structure of the data. I need to create a calculation which will multiply the 'UnitOfMeasure' (Recycled and Reused) against the associated Unit Weight for that 'AssetType'. e.g. Laptops Recycled (1438) * Laptops Unit Weight (2) = 2876

 

AssetTypevolumeUnitOfMeasure
Laptops1438Recycled
Laptops16676Reused
Laptops2Unit Weight
Monitors450Recycled
Monitors9571Reused
Monitors8Unit Weight
Printers0Recycled
Printers11Reused
Printers20Unit Weight
Server25Recycled
Server26Reused
Server12Unit Weight

 

I need to calculate the total for Recycled and Reused, in the example provided I should be returned with the total below of 6,776 Recycled and 110,320 Reused. 

 

AssetTypeRecycledReused
Laptops         2,876           33,352
Monitors         3,600           76,568
Printers0                   88
Server            300                 312
Total         6,776         110,320
1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @Raymz112 
Try this 2 formulas:

1.

recycled =
sumx(
    SUMMARIZE('Table','Table'[AssetType],"total rec",CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Recycled")*CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Unit Weight")),[total rec])
2.
reused =
sumx(
    SUMMARIZE('Table','Table'[AssetType],"total reu",CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Reused")*CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Unit Weight")),[total reu])
Ritaf1983_0-1701708329702.png

the pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

View solution in original post

2 REPLIES 2
Raymz112
Helper I
Helper I

It works, thank you for your help! 🙂

Ritaf1983
Super User
Super User

Hi @Raymz112 
Try this 2 formulas:

1.

recycled =
sumx(
    SUMMARIZE('Table','Table'[AssetType],"total rec",CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Recycled")*CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Unit Weight")),[total rec])
2.
reused =
sumx(
    SUMMARIZE('Table','Table'[AssetType],"total reu",CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Reused")*CALCULATE(sum('Table'[volume]),'Table'[UnitOfMeasure]="Unit Weight")),[total reu])
Ritaf1983_0-1701708329702.png

the pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.