DAX help request: Load Factor (sample inside)

[efofecks]

Hello all, I'm stumped with this problem.

The (simplified) data I have is as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Flight[/TD]
[TD]Cabin[/TD]
[TD]Passengers[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Business[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Economy[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Economy[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Economy[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

And I have a lookup table of capacity per flight:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Flight[/TD]
[TD]Cabin[/TD]
[TD]Capacity[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Business[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Economy[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Business[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Economy[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

How do I calculate Load Factor (passengers / capacity) per cabin? Aiming to have a result similar to the one below. I get tripped up whenever I need to combine DistinctCount and calculate filters, and get stuck when capacity exists but no passengers do. (e.g. no Business Class travelers for flight B)

Output:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Flight[/TD]
[TD]Cabin[/TD]
[TD]Passengers[/TD]
[TD]Capacity[/TD]
[TD]Load Factor[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Business[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Economy[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Business[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Economy[/TD]
[TD]3[/TD]
[TD]15[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]3[/TD]
[TD]20[/TD]
[TD]15%[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be much appreciated.

 

[skywriter]

Excel Workbook
	A	B	C	D	E
1	Flight	Cabin	Passengers
2	A	Business	1
3	A	Economy	2
4	A	Economy	3
5	B	Economy	4
6
7
8	Flight	Cabin	Capacity
9	A	Business	5
10	A	Economy	10
11	B	Business	5
12	B	Economy	15
13
14	Flight	Cabin	Passengers	Capacity	Load Factor
15	A	Business	1	5	20%
16		Economy	5	10	50%
17		Total	6	15	40%
18
19	B	Business	0	5	0%
20		Economy	4	15	27%
21		Total	4	20	20%
Sheet1

 

[efofecks]

Hi skywriter,

Thank you for your response - However, I was hoping for a DAX calculation (this is part of a larger dashboard, and sumifs won't work - I'm pulling from a database with about 15 million rows). Would you know any calculated measure that can handle the required pivot table?

Thanks,

 

[scottsen]

So... I wanted to relate these 2 tables. I'm not positive that is required, but as an initial attempt it made sense to me. To do so, I made a calculated column on each table called "Flight-Cabin" =[Flight]&"-"&[Cabin].
(In Power Pivot you can't create a relationship with multiple columns, so I made this dude... then related the tables).

Then wrote these measures:
TotalPassengerCapacity:=sum(FlightCapacity[Capacity])
TotalActualPassengers:=if (COUNTROWS(FlightData) = 0, 0, sum(FlightData[Passengers]))
LoadFactor:=DIVIDE([TotalActualPassengers],[TotalPassengerCapacity])

Lemme know what you think,
Scott

 

[efofecks]

Thanks, scottsen. I'm not in the office right now but I think the key is your TotalActualPassengers line. The actual model is slightly more complicated than this but I'll certainly try this tomorrow and get back whether it works or not!