Header and line Sum measures best practise

[jhowe1]

Hi all, 

Apologies if there is a very simple answer to this but I can't seem to find a best practise solution.  If I have two tables WorkOrder (header) and Booking (line), what is the best way to SUM a value from booking per WorkOrder? A WorkOrder doesn't necessarily always have a booking, and a WorkOrder can potentially have multiple bookings. Booking has a foreign key to WorkOrder. What is the best/most efficient DAX for measure WorkOrderDuration? Thanks.

WorkOrder			Booking
WorkOrderKey	Measure WorkOrderDuration = 'SUM(Booking[BookingDuration]) GROUPED BY WorkOrderKey'		BookingKey	WorkOrderKey	BookingDuration		Measure BookingDuration = 'SUM(Booking[BookingDuration])'
1	50		1	1	30		30
2	35		2	1	20		20
3	40		3	2	35		35
4	20		4	3	40		40
5	50		5	4	20		20
6	45		6	5	50		50
7	25		7	6	45		45
8	45		8	7	25		25
9	0		9	8	30		30
10	0		10	8	15		15

[V-lianl-msft]

Hi @jhowe1 ,

Based on your description, table WorkOrder  has a one to many relationship with table Booking. The calculation of power Bi is aggregated at the row level. When you use the columns in the WorkOrder  table to display measure, it will always aggregate at the row level based on the way of your measure expressions.

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

[amitchandak · Accepted solution]

@jhowe1 , Not able to get what is issue with SUM(Booking[BookingDuration])

It should work with columns for both work order and bookings

Any values at work order level, if need work with details van be used like

sumx(values(WorkOrder[WorkOrderKey]), calculate(Max(WorkOrder[Value])))

[jhowe1]

Hi thanks for your reply, there was no issue with SUM(Booking[BookingDuration) my question was how to sum the booking duration (line level) at header level (workorder) but maybe i am confusing things and it will just work depending on context i am in. i.e. if i am looking at table with booking key it will sum line by line, but if looking at table with work order key it will sum by work order key (grouped). Hi can you let me know if my understanding is correct?