Measure to compare actual vs capacity per Origin/Destination

[pratafran]

Hello!,

I'm trying to figure out the best approach to acomplish this:

I have a capacity of trucks per day per facility:

Facility	Capactity
A	10
B	15
C	8

And then I have a table with each individual truck executed from origin to destination and the date of execution.

I need to create a table comparing the actual trucks per day vs the capacity (%) per each day.

For acomplishing this, I did a merge of the Capacity table with a calendar table, getting for each individual day, the capacity of each facility. That table is related to a main Calendar Table, together with the actual trips table.

Then I created a % measure comparing actual vs capacity and I was able to put them into a table using the day of the calendar day.

Now I need to do the same but considering the capacity also depends on the destination:

Facility	Destination	Capactity
A	X	5
A	Y	5
B	Z	15
C	J	2
C	F	2
C	K	4

I want to compare the actual vs capacity on a route basis and being able to show that into a table.

Facility	Destination	Measure	31-Aug	01-Sep	02-Sep	03-Sep
A	X	Actual	4	3	5	5
A	X	Capacity	5	5	5	5
A	X	%	80%	60%	100%	100%
A	Y	Actual	0	0	4	5
A	Y	Capacity	5	5	5	5
A	Y	%	0%	0%	80%	100%
B	Z	Actual	10	8	14	14
B	Z	Capacity	15	15	15	15
B	Z	%	67%	53%	93%	93%
C	J	Actual	2	2	2	2
C	J	Capacity	2	2	2	2
C	J	%	100%	100%	100%	100%
C	F	Actual	2	2	2	2
C	F	Capacity	2	2	2	2
C	F	%	100%	100%	100%	100%
C	K	Actual	3	3	2	3
C	K	Capacity	4	4	4	4
C	K	%	75%	75%	50%	75%

which would be the most efficient way of achieving this?

My two options are:

1- The merge between capacity table and the calendar table (but it creates a huge table)

2- Appending new "capacity" lines the the actual facts table per each day.

The capacities are fixed per combination so I'm wondering if it can be solved directly within a measure that do the job.

Thanks!

[jgeddes]

In my example I just created summary data to get the visual built. 

For your capacity table I would be inclined to replace the facility/capacity table you have with dates with the facility/destination/capacity table. 

That way you would still be able to sum the capacity by facility and also by destination.

[jgeddes]

It is hard to say for sure without seeing how your measures are set up but assuming they calculate the required values correctly now, you should be able to adjust a matrix visual to get the result you are looking for. 

I set up a matrix with the following

Then set Values>Options>Switch values to rows = 'On'

and 

Row Headers>+/- icons = 'Off'

and 

Row Headers>Options>Stepped layout = 'Off'

to get the resulting matrix visual

[pratafran]

Thank you, my main concern is how to treat the Capacity table. For the example you provided, did you did the same than I proposed of merging the calendar table with the capacity table and getting repeated the capacity per facility per single day?. Or did you managed it directly as a measure?