Table not calculating total correctly

[GilesWalker]

Hi everyone,

I have created a number of measures to calculate something called NTK. What I am capturing is the kilometers travelled from one location to another and the total net tonnes.

The table I am using is called Consignments.

The kilometers is calculated using the following measure:

Kilometers = DIVIDE(SUM(Consignments[NewColumn.distance]),COUNTA(Consignments[Wagon type]))

The distance is divided my the count of wagon type because I need to know what the single distance travelled was. In our system each wagon type will have a distance registered to it. For example distance between point A and point B is 20km, if there are 10 wagons then each wagon has travelled 20km, total 200km, i.e 200/10 = 20km.

Next I used the follwoing measure to calculate the train load:

Train load = sumx(SUMMARIZE(Consignments,Consignments[Consignment_Id],Consignments[Tonnes]),Consignments[Tonnes])

 To calculate the NTK i created another measure:

New NTK = [Kilometers]*[Train load]

The issue I am having is that the individual routes are calculating correctly, but the total is not adding everything up. See the picture below:

The total for NTK should say 38,864,935.02 

The totals for Train load and Kilometers is also wrong. The total in each row is correct, just not the subtotal at the bottom.

I am hoping someone can tell me what I have done wrong so I have the totals calculated correctly.

Thanks,

Giles

[ImkeF]

Looks like HASONEVALUE would work for you here: http://exceleratorbi.com.au/sum-vs-sumx-in-dax/