cancel
Showing results for
Did you mean:

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

Helper I

## Summarize table and grouping values by multiple conditions in one column

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

 KpiDate AssetType UnitOfMeasure volume 30/06/2023 Docks Recycled 613 30/06/2023 Docks Reused 10,492 30/06/2023 Docks Unit Weight 1 30/06/2023 keyboard / mouse Recycled 1,219 30/06/2023 keyboard / mouse Reused 4,357 30/06/2023 keyboard / mouse Unit Weight 1 30/06/2023 Laptops Recycled 1,438 30/06/2023 Laptops Reused 16,676 30/06/2023 Laptops Unit Weight 2 30/06/2023 Mobility Recycled 20 30/06/2023 Mobility Reused 20 30/06/2023 Mobility Unit Weight 1 30/06/2023 Monitors Recycled 450 30/06/2023 Monitors Reused 9,571 30/06/2023 Monitors Unit Weight 8 30/06/2023 Networking Recycled 202 30/06/2023 Networking Reused 322 30/06/2023 Networking Unit Weight 5 30/06/2023 Printers Recycled - 30/06/2023 Printers Reused 11 30/06/2023 Printers Unit Weight 20 30/06/2023 Server Recycled 25 30/06/2023 Server Reused 26 30/06/2023 Server Unit Weight 12 30/06/2023 Storage Recycled 288 30/06/2023 Storage Reused 231 30/06/2023 Storage Unit Weight 1 30/06/2023 Systems Recycled 778 30/06/2023 Systems Reused 6,379 30/06/2023 Systems Unit Weight 3

I need to calculate the total for Recycled and Reused, in the example provided I should be returned with the total below of 12,218 Recycled and 146,299 Reused. I need to work out the row by row values for each asset type and then be able to role this up to the 'UnitofMeasure' so I can great graphs for 'Recycled' and 'Reused'

 Asset Type KPI Date UnitOfMeasure(Recycled) UnitOfMeasure(Reused) Docks 30/06/2023 631 10,492 keyboard / mouse 30/06/2023 1,219 4,357 Laptops 30/06/2023 2,876 33,352 Mobility 30/06/2023 20 20 Monitors 30/06/2023 3,600 76,568 Networking 30/06/2023 1,010 1,610 Printers 30/06/2023 - 220 Server 30/06/2023 300 312 Storage 30/06/2023 228 231 Systems 30/06/2023 2,334 19,137 Total Total 12,218 146,299

I've tried the below measure which was worked against 'AssetType' but didn't like me rolling up to 'UnitofMeasure'

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

1 ACCEPTED SOLUTION
Super User

@Raymz112

You can add an additional measure as follows this will work based on the selected UOM,

``UOM = IF( SELECTEDVALUE( Table7[UnitOfMeasure] ) = "Recycled" , [UnitOfMeasure(Recycled)] , [UnitOfMeasure(Reused)] ) ``

Did I answer your question? Mark my post as a solution! and hit thumbs up
8 REPLIES 8
Resident Rockstar

Hello @Raymz112 ,

You can use the below measure to get the desired result

1. First Pivot your table such that Recycled, Reused and Unit_Weight are separate columns

2. Create the below 2 measures

``Measure4 = SUMX('Table', 'Table'[Recycled] * 'Table'[Unit Weight])``
``Measure5 = SUMX('Table', 'Table'[Reused] * 'Table'[Unit Weight])``

Regards,

Helper I

Hello,

Thank you for your help on this but I need to keep the data model the same. Do you know of a solution which doesn't involve pivoting?

Super User

@Raymz112

Create the following three measures and add it to a matrix viz, make sure you have Asset Type alrady added in your Matrix Row section:

``````KPI Date =  CALCULATE( MAX( Table7[KpiDate] ) )
------------------------------------------------------
UnitOfMeasure(Reused) =
SUMX(
VALUES(Table7[AssetType]),
VAR __Reused = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Reused" )
VAR __UnitWeigh = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Unit Weight" )
VAR __Result = __Reused * __UnitWeigh
RETURN
__Result
)
-------------------------------------------------------
UnitOfMeasure(Recycled) =
SUMX(
VALUES(Table7[AssetType]),
VAR __Recycled = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Recycled" )
VAR __UnitWeigh = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Unit Weight" )
VAR __Result = __Recycled * __UnitWeigh
RETURN
__Result
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper I

Hello,

Thank you for your help, unfortunately it doesn't work as the totals at the bottom are incorrect. I also want the associated value to show next to the 'UnitofMeasure' so that I can use it in my visuals, displaying the difference between the two.

The below is what I got from your calculation

Super User

@Raymz112

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper I

The update works as shown below but then doesn't allow me to filter the data from the 'UnitofMeasure' column which I need for my visualisations. I ideally want all of them to be in one measure and filterable by 'UnitofMeasure' being either 'Recycle' or 'Reused'

Super User

@Raymz112

You can add an additional measure as follows this will work based on the selected UOM,

``UOM = IF( SELECTEDVALUE( Table7[UnitOfMeasure] ) = "Recycled" , [UnitOfMeasure(Recycled)] , [UnitOfMeasure(Reused)] ) ``

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper I

Smashed it - thank you! 🙂

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors