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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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