Measure at many to one relationship not working

[manutejedor]

Hi,

I have an issue when using Measures, to simplify it I have made the following example. I have the following 2 tables:

Table "RACES"

RACE	VEHICLE	DISTANCE (km)	TIME (h)
1	ALPHA	10	0,2
2	ALPHA	20	0,5
3	BRAVO	15	0,5
4	BRAVO	30	0,8
5	BRAVO	25	0,7
6	CHARLIE	25	0,6
7	CHARLIE	50	1,3

Table "DRIVERS";

RACE	VEHICLE	DRIVERS
1	ALPHA	JOHN
1	ALPHA	MIKE
2	ALPHA	JOHN
2	ALPHA	PETER
3	BRAVO	TOM
3	BRAVO	PETER
4	BRAVO	MIKE
4	BRAVO	PETER
5	BRAVO	JOHN
5	BRAVO	MIKE
6	CHARLIE	MIKE
7	CHARLIE	PETER
7	CHARLIE	TOM

In table RACES, I have 7 different races with a distance and time for each one, these 7 races are performed by 3 different cars. Each of these cars can be driven by 1 or 2 drivers, this information is in table "DRIVERS". Both tables are linked by the field RACE.

In PBI I have made a Measure in table RACES called "SPEED" = sum(RACES[DISTANCE (km)])/sum(RACES[TIME (h)]). This Measure calculates correctly when displayed at Race level:

 

The problem is when I want to see the SPEED by DRIVER (I intend to see average speed of all races in which the driver has participated):

As you can see, the measure is not properly displayed. Another thing I want to do is to filter by DRIVER and see all the races in which the specified driver has participated with its SPEEDS. This doesn't work either:

Any solution on that? I could share the sample .pbix file but I don't know how to do this in this forum XD, let me know if you need it.

Thanks you all!!

Manu

[iamprajot · Accepted solution]

Speed = CALCULATE(SUM(RACES[DISTANCE (km)])/SUM(RACES[TIME (h)]),DRIVERS)

[manutejedor]

That worked! Thanks!!

[JoHo_BI]

Hi Manu,

Since you're not storing the distance and time at driver level, you won't be able to figure them out. These metrics are for the races, hence why they would work at this level. If you were to store the time at driver level (eg. each drivers total time), you'd be able to use that then look against the race distance to get their speed, as well as the average speed per race.

Hope that helps!

[manutejedor]

Ok, I understand that. But can you offer any solution? For example, i know that JOHN has participated in Races 1, 2 and 5, and I know the SPEED of each one of this races, there is no way to filter by DRIVER = JOHN and see these 3 races with their SPEEDS?

Manu