DAX - First Average, then Sum

[GreenP]

Hi Experts,

I have a table like below:

Shipment Number	Material Number	Distance	Item Weight
S1	M001	100	10
S1	M002	100	10
S1	M003	100	10
S2	M004	200	10
S2	M005	200	50
S3	M006	300	10
S3	M007	300	10
S3	M008	300	50
S4	M009	400	80
S5	M010	500	10
S5	M011	500	10

Each shipment can have multiple materials.

We need DAX to calculate the following measures:

• Total distance covered by all shipments, which, in the above table, should be (100+200+300+400+500) = 1500 Km
• Total weight = 260 Kg
• Total weight/Km = 260/1500

If someone can help with the DAX, it would be very helpful.

Thanks

[johnt75]

You can use

Total distance covered =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Shipment number], 'Table'[Distance] ),
    'Table'[Distance]
)

Total Weight = SUM( 'Table'[Item Weight])

Total Weight/KM = DIVIDE( [Total Weight], [Total distance covered])

[SwahamD26]

Hi, The SUMX function does not accept a field as its 3rd argument. We need an aggregated value there. Can you help us?

[johnt75]

There isn't a third argument to SUMX, I think you may have a typo in your code. Double check it against what I posted.