Measure Expression Problem

[juanconsuela123]

Hi, I was having a problem on calculating a measure, where I want to know the utilisation percentage of a truck by customer. Here is the table example:

Customer Name	Item Weight	Vehicle	Vehicle Type	Vehicle Space
A	500	ABC	SINGLE TIRE	2000
A	600	ABC	SINGLE TIRE	2000
A	700	ABC	SINGLE TIRE	2000
A	1000	CDE	DOUBLE TIRE	4000
A	2000	CDE	DOUBLE TIRE	4000
B	300	FGH	SINGLE TIRE	2000
B	400	FGH	SINGLE TIRE	2000
B	500	FGH	SINGLE TIRE	2000
B	750	IJK	DOUBLE TIRE	4000
B	600	IJK	DOUBLE TIRE	4000

VEHICLE	WEIGHT	VEHICLE SPACE	TRUCK UTILISATION
ABC	1800	2000	90%
CDE	3000	4000	75%
FGH	1200	2000	60%
IJK	1350	4000	34%

CUSTOMER NAME	AVG TRUCK UTL
A	83%
B	47%

[Anonymous · Accepted solution]

Hi @juanconsuela123 ,

I suggest you to try code as below to create measures.

TRUCK UTILISATION = 
VAR _ITEM =
    CALCULATE ( SUM ( 'Table'[Item Weight] ) )
VAR _SPACE =
    CALCULATE ( MAX ( 'Table'[Vehicle Space] ) )
RETURN
    DIVIDE ( _ITEM, _SPACE )

AVG TRUCK UTL = 
AVERAGEX (
    SUMMARIZE ( 'Table', 'Table'[Customer Name], 'Table'[Vehicle] ),
    [TRUCK UTILISATION]
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @juanconsuela123 ,

without any hint about calculating the measure, it's hard to provide a DAX statement.

Regards,

Tom