Summarize table and grouping values by multiple conditions in one column

[Anonymous]

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

[Thejeswar]

Hello @Anonymous ,

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,

[Anonymous]

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? 

[Fowmy]

@Anonymous 

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
)

[Anonymous]

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

[Fowmy]

@Anonymous 

Please check the reply, I just updated it

[Anonymous]

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'

[Fowmy · Accepted solution]

@Anonymous 

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

You can add this to your chart 

[Anonymous]

Smashed it - thank you! 🙂