How to show specific values of the data in a lookup table instead of total sum in dax measure?

[Anonymous]

Hi guys,

Im a beginner that is a bit stuck with probably something simple. I think im using the wrong function but I managed to only find the ones that didn't work. Hope some one can help!

I have a lookup table (truck capacity) where I have all truck numbers and the belonging loading capacity. So:

Truck number - Loading capacity

Truck 1 - 3,0 

Truck 2 - 5,0

Truck 3 - 12,0

Than I have a data table (routes) where I have all truck numbers by days, truck number and loading meters. So:

Date - Truck number - Loading meters

1-1-20 - Truck 1 - 1,8

1-1-20 - Truck 2 - 4,0

1-1-20 - Truck 3 - 9,2

2-1-20 - Truck 1 - 2,4

2-1-20 - Truck 2 -  2,0

2-1-20 - Truck 3 - 10,6

My look up table has a relationship with the data table by truck number. That having said.

My end goal is to divide loading meters by loading capacity for each truck by route by day.

I already have the measure for the loading meters using sum('routes'[Loading meters]).

Now my next step is to have a measure for loading capacity. I tried:

- SUM('truck capacity'[Loading capacity])

and

- CALCULATE(

SUM('truck capacity'[Loading capacity]),

VALUES('truck capacity'[Truck number])

)

My problem is that it now shows the total sum of the three truck numbers as values (20,00), no matter for what truck number I want to use it. So basically its summarized but I want the loading capacity per truck. Can someone tell me what my correct measure is for loading capacity based on truck number instead of what I have now done to show the total loading capacity of all trucks...

Thanks in advance! Grtz Jeroen

[Anonymous · Accepted solution]

Managed to solve my problem. 

Everything with the DAX calculation was okay.

Made the rookie mistake of using the licenseplate of my data table instead of using the licenseplate of my look up table. When I changed them it was working correctly.

[rfigtree]

Hi I think this is what you mean, you will need to expand your fact table to include route and have an entry for each trip otherwise you will get wonky results.

Think it is right, gives you ideas anyway.

Dont know how to save file here so pictures will have to do.