Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
Proud to be a Super User! | |
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
Proud to be a Super User! | |
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |